I have sent of function library where I store all the UDF I have ever written. Recently I received email from my friend requesting if I have UDF which manipulate string and returns only very first word of the statement. Well, I realize that I do not have such a script at all. I found myself writing down this similar script after long time. Let me know if you know any other better script to do the same task.
DECLARE @StringVar VARCHAR(100)
SET @StringVar = ' anything '
SELECT CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END FirstWordofSentence
Above script can be converted to User Defined Function which you can later use in SELECT statement as well. Here is how you can convert it to UDF and use it afterwords.
CREATE FUNCTION GetFirstWord (@StringVar VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @rtStringVar VARCHAR(MAX)
SELECT @rtStringVar = CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END
RETURN @rtStringVar
END
GO
--Usage of Function
SELECT dbo.GetFirstWord ('') AS FirstWordofSentence;
SELECT dbo.GetFirstWord (' Space Before') AS FirstWordofSentence;
SELECT dbo.GetFirstWord ('Long Statement') AS FirstWordofSentence;
SELECT dbo.GetFirstWord ('SingleWord') AS FirstWordofSentence;
You can see in the resultset where it will find the very first word of the any sentence.
Reference: Pinal Dave (http://blog.sqlauthority.com)
