STRING_SPLIT – New function in SQL Server 2016

SQL Server Concepts

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


STRING_SPLIT('this,is,a,mango,,tree', ',');


Here is the output.

You can read more about STRING_SPLIT here.

Author: simpleBIinsights

Leave a Reply