Splitting strings in SQL Server

One of the query pains in SQL Server involves a simple split of a string. You want to pass into a stored procedure a comma delimited list of items and use that in your procedure. In order to get a tabular result from that string, you had to write your own function. But no longer. SQL Server 2016 now provides two methods for splitting a string.

Erik Darling describes the String_split function (https://www.brentozar.com/archive/2016/03/splitting-strings-sql-server-2016-rescue/):
declare @list nvarchar(200) = N'1,2,3,4,5';

select * from string_split(@list,N',') as ss
This amazing new function takes the delimiter and splits the string into rows. As Erik points out, this works very nicely using the apply key word.
declare @t table (list nvarchar(200));

insert @t values (N'1,2,3,4,5');

select * from @t t
CROSS APPLY
string_split(t.list,',') as ss

Microsoft doesn’t like to just provide one way to achieve the same goal. You can do the same thing using the OPENJSON function.
declare @list nvarchar(200) = N'1,2,3,4,5';

select * from
OPENJSON (N'[' + @list + ']', '$')
WITH ( Number int N'$'

) as arr ;
select * from string_split(@list,',') as ss

declare @t table (list nvarchar(200));

insert @t values (N'1,2,3,4,5');

select * from @t t
CROSS APPLY OPENJSON (N'[' + t.list + ']', '$')
WITH ( Number int N'$') as arr ;

select * from @t t
CROSS APPLY
string_split(t.list,',') as ss

I have not tested to see which performs better. However, it is great to be able to put aside my custom splitting code.

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in TSQL. Bookmark the permalink.

Leave a Reply