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.
STRING_SPLIT is a new T-SQL function that splits an input string by a separator and outputs the results as a table.
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 splitting function|
|SELECT * FROM STRING_SPLIT('SQL SERVER 2016 ROCKS', ' ')|
You can also use this function to split comma separated value string in existing columns –
|DROP TABLE IF EXISTS dbo.EmployeeSkills|
|–Create a sample table|
|CREATE TABLE dbo.EmployeeSkills|
|EmployeeID INT IDENTITY PRIMARY KEY,|
|INSERT INTO dbo.EmployeeSkills|
|(FirstName, Email, Skills) VALUES|
|('Samir', 'firstname.lastname@example.org', '.Net, SQL' ),|
|('Peter', 'email@example.com', 'Java, COBOL' ),|
|SELECT * FROM dbo.EmployeeSkills|
|— Split comma separated value string in a table column|
|SELECT EmployeeID, FirstName, Email , value|
|CROSS APPLY string_split(Skills, ',')|
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.