My Technical Notes

Monday, 11 August 2014

SQL Server: Getting First Word of a String

Below if a function which returns us the first word of a string (i.e. part of the string before a space character):


IF OBJECT_ID (N'dbo.FirstWord', N'FN') IS NOT NULL DROP FUNCTION dbo.FirstWord;

CREATE FUNCTION dbo.FirstWord (@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ret NVARCHAR(MAX)
    DECLARE @spaceInd INT = CHARINDEX(' ', @str)
    IF @spaceInd = 0
        SET @ret = @str
    ELSE    
        SET @ret = SUBSTRING(@str, 1, @spaceInd - 1)
    RETURN(@ret)
END

And we use it like so:


SELECT dbo.FirstWord('Tahir Hassan') -- = 'Tahir'

No comments: