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 '% %';
name | value |
---|---|
New Jersey | New |
New Jersey | Jersey |
North Carolina | North |
North Carolina | Carolina |
Royal Oak | Royal |
Royal Oak | Oak |
Royal Sovereign | Royal |
Royal Sovereign | Sovereign |
South Dakota | South |
South Dakota | Dakota |