Get First or Second Word using T-SQL

When working with SQL (T-SQL) I often need to work with words with-in a string (nvarchar or varchar). For instance I want to display only the first word (first name in this case) of a field (FullName in this case) or maybe the second word in other cases. Words are funny things, they can be separated by commas, spaces, hyphens, and other characters such as semi-colons, exclamation point, parenthesis, brackets perhaps.

Below is the code to create a Scalar-valued Functions in SQL Server (T-SQL) that get the first word or second word.

 

Get First Word

create function GetFirstWord(@Name as nvarchar(100))
returns nvarchar(100)
as
BEGIN

Declare @FirstWord as nvarchar(100)
Declare @SpaceIdx as int

-- convert word separators to space for simplicity
Set @Name = Replace(@Name, ',', ' ') -- comma to space
Set @Name = Replace(@Name, '.', ' ') -- period to space
Set @Name = Replace(@Name, '-', ' ') -- hyphen to space

Set @SpaceIdx = CHARINDEX(' ', @Name)

if (@SpaceIdx > 0)
    SET @FirstWord = SUBSTRING(@Name, 0, @SpaceIdx)
else -- all one word
    SET @FirstWord = @Name

return @FirstWord;
END;

 

Get Second Word

create function GetSecondWord(@Name as nvarchar(100))
returns nvarchar(100)
as
BEGIN

Declare @SecondWord as nvarchar(100)
Declare @AfterWord1 as nvarchar(100)
Declare @SpaceIdx as int
Declare @Space2Idx as int

-- convert word separators to space for simplicity
Set @Name = Replace(@Name, ',', ' ') -- comma to space
Set @Name = Replace(@Name, '.', ' ') -- period to space
Set @Name = Replace(@Name, '-', ' ') -- hyphen to space

Set @SpaceIdx = CHARINDEX(' ', @Name)

if (@SpaceIdx = 0) return ''

Set @AfterWord1 = SUBSTRING(@Name, @SpaceIdx+1, len(@Name) - @SpaceIdx)

Set @Space2Idx = CHARINDEX(' ', @AfterWord1)

if (@Space2Idx > 0)
    SET @SecondWord = SUBSTRING(@AfterWord1, 0, @Space2Idx)
else -- remainder of word
    SET @SecondWord = @AfterWord1

-- if second word is the same as the original, then we only have one word
if @SecondWord = @Name return ''

return @SecondWord;
END;

Usage

select GetFirstWord(‘Joe Smith’)

returns: Joe

select GetSecondWord(‘Joe Smith’)

returns: Smith

 

Extending the Code

In the example above was just concerned with with commas, spaces, and hyphens, but you can extend the code quite easily by adding additional replace statements.

 

Note on Performance

I have implemented these functions as Scalar-valued functions which are known to incur a noticeable performance hit when you use them in a select statement.