As a SQL Server developer, you may often come across situations where you need to manipulate strings in your queries. One common requirement is to extract the first word from a sentence or statement. In this article, we will discuss a simple script and a user-defined function that can help you achieve this task.

The Script Approach

Let’s start with a script that extracts the first word from a given string:

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 AS FirstWordofSentence

This script first trims any leading or trailing spaces from the input string using the LTRIM function. It then checks if there is a space character in the string using the CHARINDEX function. If there is no space, it returns the trimmed string as the first word. Otherwise, it uses the SUBSTRING function to extract the substring from the start of the string to the first space character, excluding the space itself.

The User-Defined Function Approach

If you find yourself frequently needing to extract the first word from a string, you can convert the script into a user-defined function (UDF) for reusability. Here is how you can do it:

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

Once the function is created, you can use it in your queries like this:

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;

The function takes a string as input and returns the first word of the sentence. You can see the results in the query output, where it correctly identifies the first word of each sentence.

Conclusion

Manipulating strings is a common task in SQL Server development. In this article, we discussed a script and a user-defined function that can help you extract the first word from a sentence. Whether you choose to use the script or the function depends on your specific requirements and the level of reusability you need. Feel free to experiment with these approaches and adapt them to your own projects.