Extract the first word of a string in a SQL Server query

tsql

SELECT CASE CHARINDEX(' ', @Foo, 1)
     WHEN 0 THEN @Foo -- empty or single word
     ELSE SUBSTRING(@Foo, 1, CHARINDEX(' ', @Foo, 1) - 1) -- multi-word
END

You could perhaps use this in a UDF:

CREATE FUNCTION [dbo].[FirstWord] (@value varchar(max))
RETURNS varchar(max)
AS
BEGIN
    RETURN CASE CHARINDEX(' ', @value, 1)
        WHEN 0 THEN @value
        ELSE SUBSTRING(@value, 1, CHARINDEX(' ', @value, 1) - 1) END
END
GO -- test:
SELECT dbo.FirstWord(NULL)
SELECT dbo.FirstWord('')
SELECT dbo.FirstWord('abc')
SELECT dbo.FirstWord('abc def')
SELECT dbo.FirstWord('abc def ghi')

I wanted to do something like this without making a separate function, and came up with this simple one-line approach:

DECLARE @test NVARCHAR(255)
SET @test = 'First Second'

SELECT SUBSTRING(@test,1,(CHARINDEX(' ',@test + ' ')-1))

This would return the result "First"

It's short, just not as robust, as it assumes your string doesn't start with a space. It will handle one-word inputs, multi-word inputs, and empty string or NULL inputs.

Enhancement of Ben Brandt's answer to compensate even if the string starts with space by applying LTRIM(). Tried to edit his answer but rejected, so I am now posting it here separately.

DECLARE @test NVARCHAR(255)
SET @test = 'First Second'

SELECT SUBSTRING(LTRIM(@test),1,(CHARINDEX(' ',LTRIM(@test) + ' ')-1))

Adding the following before the RETURN statement would solve for the cases where a leading space was included in the field:

SET @Value = LTRIM(RTRIM(@Value))

Extract the first word of a string in a SQL Server query

Related

Recent Posts