STRING_SPLIT function

The STRING_SPLIT function performs the inverse operation for 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',' ');

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);

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 '% %';