What's the best way to extract the first word of a string in sql server query?
+5
A:
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')
Marc Gravell
2009-04-01 22:37:29
Thanks for this UDF - I noticed that select dbo.[FirstWord](' abc def') will return '' In most cases you probably want to return 'abc'
Imageree
2010-06-19 13:27:21