How to Split a String in SQL Server

Problem:

You want to split a string in SQL Server. There are several methods you can use to accomplish this. Below, we will go over a few practical approaches.

Example 1: Split a sentence by space

You have a sentence, and you'd like to split it by the space character.

Solution 1:

SELECT value
FROM STRING_SPLIT('An example sentence.', ' ');

The result looks like this:

Discussion:

The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and the space (' ') as the second argument.

STRING_SPLIT() results in a column named value. To get each part of the string in a separate row, select value from STRING_SPLIT(string, separator). For example,

SELECT value
FROM STRING_SPLIT('An example sentence.', ' ');

Of course, you can split a string using some other separator, e.g., the comma. You can also rename the column just like any other column.

Example 2: Split multiple rows by space

In the texts table, there are some sentences.

You want to split the sentences by the space character.

Solution 2:

SELECT value
FROM texts
CROSS APPLY STRING_SPLIT(sentence, ' ');

The result looks like this:

Discussion:

Just as in the previous example, the STRING_SPLIT(text, separator) function splits the string given as the first argument by the separator. This time, you have a few sentences to take care of; these sentences are stored in the texts table. This is why you need to use CROSS APPLY; more specifically,

texts CROSS APPLY STRING_SPLIT(sentence, ' ')

Use it in the FROM clause. It means that the right side (STRING_SPLIT(sentence, ' ')) is applied to each row of the left-side table (texts). This is why the right side can use the columns from the left-side table (here, the sentence column from the texts table.) Here's the query you get.

SELECT value
FROM texts
CROSS APPLY STRING_SPLIT(sentence, ' ');

Example 3: Splitting strings with two columns

In the texts table, there are two columns: id and sentence.

You want to split the sentences by the space character and also show the IDs of the sentences.

Solution 3:

SELECT
  id,
  value
FROM texts
CROSS APPLY STRING_SPLIT(sentence, ' ');

The result looks like this:

Discussion:

This example is very similar, but you also want to see the id column. To see this column, just add it to the SELECT list and remember to include the comma. You'll see the ID of the sentence along with the parts of the sentences in the result. For example, the first sentence is split into 5 parts and has the ID 1. Hence, the ID for all 5 parts in the result table will be 1. The next sentence, with the ID 2, is also split into 5 parts, and each of these parts will be shown with id = 2.