Back to cookbooks list Articles Cookbook

How to Split a String in SQL Server

Database:

Operators:

  • STRING_SPLIT
  • CROSS APPLY

Table of Contents

Problem:

You want to split a string in SQL Server.

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:

value
An
example
sentence.

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.

sentence
This is the first sentence.
And here's the other one.

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:

value
This
is
the
first
sentence.
And
here's
the
other
one.

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.

idsentence
1This is the first sentence.
2And here's the other one.

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:

idvalue
1This
1is
1the
1first
1sentence.
2And
2here's
2the
2other
2one.

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.

Recommended courses:

Recommended articles:

See also: