SQL Server - Extracting All The Words From a String In An SQL Query

Extracting All The Words From a String In A SQL Server Database Query

It is easy enough to extract the first word from a string in a database query, but what if you need to separate all words in a given string into a list of separate words?

This is a job for Common Table Expressions (CTEs) as they enable us to repeatedly read a single record while changing it each time.

Here is a SQL Server example which takes a string variable and spearates it into inidividual words. We use a combination of CHARINDEX, LEFT and RIGHT functions to achieve it.

DECLARE @StringValue VARCHAR(200) = 'This is a string of words i want to separate';

WITH SeparateWords ( StringValue, Word, Position, RestOfLine)
AS
       (
	       SELECT  @StringValue
                     , CASE CHARINDEX(' ',@StringValue)
                            WHEN 0 THEN @StringValue
                            ELSE LEFT(@StringValue,  CHARINDEX(' ',@StringValue) -1)

                       END
                     , 1
                     , CASE CHARINDEX(' ',@StringValue)
                           WHEN 0 THEN ''
                           ELSE RIGHT(@StringValue, LEN(@StringValue) - CHARINDEX(' ',@StringValue))
                       END
      	   UNION ALL

           SELECT  sw.StringValue
                     , CASE CHARINDEX(' ',RestOfLine)
                           WHEN 0 THEN RestOfLine
                           ELSE LEFT(RestOfLine, CHARINDEX(' ',RestOfLine) -1)
                       END
                     , Position + 1
                     , CASE CHARINDEX(' ',RestOfLine)
                           WHEN 0 THEN ''
                           ELSE RIGHT(RestOfLine, LEN(RestOfLine) -
									  CHARINDEX(' ',RestOfLine))
                       END
           FROM SeparateWords AS sw
           WHERE sw.RestOfLine != ''
       )
SELECT * FROM SeparateWords
GO

The following screen shot shows the results from the query:

The following version would extract just the last word of thre given string:

DECLARE @StringValue VARCHAR(200) = 'This is a string of words I want to separate';

WITH SeparateWords ( StringValue, Word, Position, RestOfLine)
AS
       (
	       SELECT  @StringValue
                     , CASE CHARINDEX(' ',@StringValue)
                            WHEN 0 THEN @StringValue
                            ELSE LEFT(@StringValue,
									  CHARINDEX(' ',@StringValue) -1)

                       END
                     , 1
                     , CASE CHARINDEX(' ',@StringValue)
                           WHEN 0 THEN ''
                           ELSE RIGHT(@StringValue, LEN(@StringValue) - CHARINDEX(' ',@StringValue))
                       END
      	   UNION ALL

           SELECT  sw.StringValue
                     , CASE CHARINDEX(' ',RestOfLine)
                           WHEN 0 THEN RestOfLine
                           ELSE LEFT(RestOfLine, CHARINDEX(' ',RestOfLine) -1)
                       END
                     , Position + 1
                     , CASE CHARINDEX(' ',RestOfLine)
                           WHEN 0 THEN ''
                           ELSE RIGHT(RestOfLine, LEN(RestOfLine) -  CHARINDEX(' ',RestOfLine))
                       END
           FROM SeparateWords AS sw
           WHERE sw.RestOfLine != ''
       )
SELECT Word AS 'Last Word', StringValue
FROM SeparateWords sw1
WHERE Position =
       (SELECT MAX(Position) FROM SeparateWords sw2
        WHERE sw1.StringValue = sw2.StringValue)

These queries could be adapted to query a character column from a set of database table records.

In a later blog I will show you how to create a dictionary word list from column values in a table.

You can find out more about database querying by attending our SQL Server Database Querying Training Courses and Transact SQL programming Courses.