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.