STRING_SPLIT function in SQL Server 2016

By Samir Behara on October 31, 2017 • ( 0 )

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 splitting function
	SELECT * FROM STRING_SPLIT('SQL SERVER 2016 ROCKS', ' ')

view raw

String Split Function in SQL Server 2016

hosted with ❤ by GitHub

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
	(
	EmployeeID INT IDENTITY PRIMARY KEY,
	FirstName VARCHAR(50),
	Email VARCHAR(50),
	Skills VARCHAR(100)
	)


	INSERT INTO dbo.EmployeeSkills
	(FirstName, Email, Skills) VALUES
	('Samir', 'samirbehara@gmail.com', '.Net, SQL' ),
	('Peter', 'peterdenner@yahoo.com', 'Java, COBOL' ),
	('Dennis','dennisknox@hotmail.com','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, ',')

view raw

Split comma separated string in SQL Server 2016

hosted with ❤ by GitHub

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