STRING_SPLIT function – SQL Server 2016

Finally, STRING_SPLIT function, one of the most awaiting feature for a long time, has been introduced by Microsoft. In SQL Server 2016, now, we have a built-in system function to split a string with a specified separator.

Earlier, whenever we had to map a denormalized string (e.g. comma separated string values) with the values stored in a table column (normalized values) , we had to create a table valued function which could create a table from a given string value by splitting it using predefined separator. The problem with creating our own function to split the string was its performance, especially if we were not using either a CLR function or an highly optimized SQL function.

One of the most common scenario where we need this functionality very frequently is whenever we need to deal with SSRS’s multi value parameters. Reporting services supplies a comma separated string value to the SQL query in case we are using a multi value parameter. Then, in the SQL query, we need to split this string based on comma to match each value with the values stored in a table column. Depending on the values in the report parameter and report execution frequency, if we are using an inefficient function to split the string, it might drastically degrade the performance of the overall report rendering.

Now that, we have an in-built system function named STRING_SPLIT, lets explore this in a bit more detail here.

STRING_SPLIT function

STRING_SPLIT function can be used to split a string using a specified separator. Below is the syntax of the function:

STRING_SPLIT ( <string> , <separator> )

Where,

<string> is a character string which could be of any type e.g. nvarchar, varchar, nchar or char, and

<separator> is a single character string of any type e.g. nvarchar(1), varchar(1), nchar(1) or char(1).

This function returns a single column table with a column name “value“. If any of the input argument (<string>, or <separator>) is an UNICODE character type (nvarchar, or nchar), the column data type will be nvarchar, otherwise it will be varchar (for non UNICODE arguments). Also, the length of the column is equal ti the length of the input string (<string>) argument length.

If you want to read more on this topic, here is the MSDN link.

Example

Before, we start talking on the limitations of this function, lets have a look on the below example which demonstrate the use of STRING_SPLIT function:

SELECT * FROM STRING_SPLIT('a,b,c,d,e,f,g,xyz', ',')
GO

Output:

Limitations

STRING_SPLIT function has few limitations as below:

1. The minimum database compatibility level to use this function is 130.
2. Till now, only single character separator is allowed which restricts the use of this function if we need to deal with multi value separators.

Thank you for the reading. Please share your input.