STRING_SPLIT function in SQL Server 2016


Problem Statement
As a developer, have you ever written your own customized string splitting function in your code? Do you still have comma separated values stored inside your database columns?

If the answer to any of these questions in YES, then you will be happy to know that with SQL Server 2016, we have finally got a built-in function which takes care of this overhead.

Resolution
STRING_SPLIT is a new T-SQL function that splits an input string by a separator and outputs the results as a table.

Syntax
STRING_SPLIT ( string , separator )

string is an expression of any character type(char, nchar, varchar, nvarchar)
separator is a single character expression of any character type (nvarchar(1), varchar(1), nchar(1) or char(1)) that is used as separator for concatenated strings.

It returns a single-column table with substrings.

String Split

You can also use this function to split comma separated value string in existing columns –

String Split 2

The string splitting function was one of the most awaited SQL functions.

Things to note

–> The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function.

–> The function accepts single character delimiter. If you try to specify multi-character delimiter, then you will get an error stating —

Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

–> I have used STRING_SPLIT() function extensively for some performance testing, but did not see any explicit issues worth mentioning.

Aaron Bertrand has a nice blog-post where he compares the performance of STRING_SPLIT() function with other traditional T-SQL approaches, and finally concludes that the STRING_SPLIT() function is the most optimal. Check it out here.
https://sqlperformance.com/2016/03/t-sql-queries/string-split

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s