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.

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

String Split

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
FirstName VARCHAR(50),
Email VARCHAR(50),
Skills VARCHAR(100)
INSERT INTO dbo.EmployeeSkills
(FirstName, Email, Skills) VALUES
('Samir', '', '.Net, SQL' ),
('Peter', '', 'Java, COBOL' ),
('Dennis','','Oracle, C#');
SELECT * FROM dbo.EmployeeSkills
— Split comma separated value string in a table column
SELECT EmployeeID, FirstName, Email , value
FROM dbo.EmployeeSkills
CROSS APPLY string_split(Skills, ',')

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.

Categories: SQL Server, SQL Server 2016

Tags: ,

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

%d bloggers like this: