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

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

From stackoverflow

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

  Imageree : Thanks for this UDF - I noticed that select dbo.[FirstWord](' abc def') will return '' In most cases you probably want to return 'abc'