STRING_SPLIT function

STRING_SPLIT function

The STRING_SPLIT function performs the inverse operation of STRING_AGG. It takes a character string and breaks it up into substrings using the separator symbol passed to it as the second argument. These substrings form a one-column table. The single column of this table can be referred to as value.

SELECT * FROM STRING_SPLIT('0 1 2 3 4 5 6 7 8 9',' ');
value
0
1
2
3
4
5
6
7
8
9

In fact, the STRING_SPLIT function acts as a non-standard single-column table value constructor. The standard alternative looks as follows:

SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) X(value);
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

A more interesting case is passing the value of a column of a table to the function.

For instance, let’s assume we have to break up the multi-word names in the Ships table into single words. The following query could be used for that:

SELECT name, value from Ships CROSS APPLY STRING_SPLIT(name,' ')
WHERE name LIKE '% %';
namevalue
New JerseyNew
New JerseyJersey
North CarolinaNorth
North CarolinaCarolina
Royal OakRoyal
Royal OakOak
Royal SovereignRoyal
Royal SovereignSovereign
South DakotaSouth
South DakotaDakota