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

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.

Marc's answer got me most of the way to what I needed, but I had to go with patIndex rather than charIndex because sometimes characters other than spaces mark the ends of my data's words. Here I'm using '%[ /-]%' to look for space, slash, or dash.

Select race_id, race_description
    , Case patIndex ('%[ /-]%', LTrim (race_description))
        When 0 Then LTrim (race_description)
        Else substring (LTrim (race_description), 1, patIndex ('%[ /-]%', LTrim (race_description)) - 1)
    End race_abbreviation
from tbl_races

Results...

race_id  race_description           race_abbreviation
-------  -------------------------  -----------------
1        White                      White
2        Black or African American  Black
3        Hispanic/Latino            Hispanic

Caveat: this is for a small data set (US federal race reporting categories); I don't know what would happen to performance when scaled up to huge numbers.

DECLARE @string NVARCHAR(50)

SET @string = 'CUT STRING'

SELECT LEFT(@string,(PATINDEX('% %',@string)))

Extract the first word from the indicated field:

SELECT SUBSTRING(field1, 1, CHARINDEX(' ', field1)) FROM table1;

Extract the second and successive words from the indicated field:

SELECT SUBSTRING(field1, CHARINDEX(' ', field1)+1, LEN (field1)-CHARINDEX(' ', field1)) FROM table1;