As part of SQL Server 2016 enhancements, a new built-in table-valued function STRING_SPLIT() is introduced. This is one of the many enhancements introduced in SQL Server 2016 like CREATE OR ALTER, STRING_ESCAPE, STRING_SPLIT.
STRING_SPLIT() is a table-valued function which splits the input string based on a specified separator and returns the output values in the form of table.
Syntax of STRING_SPLIT is
STRING_SPLIT ( input, separator )
- input is a string of NVARCHAR, VARCHAR, NCHAR, or CHAR.
- separator is a single character used as a separator for splitting.
Please note that STRING_SPLIT requires the compatibility level to be at least 130.
STRING_SPLIT takes a string that has delimited substrings as input, and takes one character to use as the delimiter or separator. STRING_SPLIT outputs a single-column table whose rows contain the sub strings. The name of the output column is value.
The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value).
STRING_SPLIT() function example
Using the STRING_SPLIT() function to split comma-separated value string. This example uses STRING_SPLIT to separate values from a comma separated string
SELECT
value
FROM
STRING_SPLIT('this,is,a,mango,,tree', ',');
Here is the output.
You can read more about STRING_SPLIT here.
