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.

The following screen shot shows the results from the query:

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

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.