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

Question

What's the best way to extract the first word of a string in sql server query?

Answer 1

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') 

Answer 2

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.