Combine string_split column results in table SQL

Question

I'm trying to create a stored procedure for updating a table in a batch. I want to take parameters in as a nvarchar and call string_split on them.

@ParamList1 NVARCHAR(max) = '1,2,3,4,5'
@ParamList2 NVARCHAR(max) = 'a,b,c,d,e'

I want to get a temporary table like

Param1  Param2
1       a
2       b
3       c
...

How would I do this?

Gordon Linoff · Accepted Answer · 2019-09-21T20:14:44.543

2

Unfortunately, string_split() does not guarantee ordering or provide a position argument (Microsoft are you listening?).

So, the safest method is a recursive CTE (or perhaps another approach using XML):

with cte as (
      select convert(nvarchar(max), NULL) as x1, convert(nvarchar(max), NULL) as x2, @paramlist1 as rest1, @paramlist2 as rest2, 1 as lev
      union all
      select convert(nvarchar(max), left(rest1, charindex(',', rest1 + ',') - 1)),
             convert(nvarchar(max), left(rest2, charindex(',', rest2 + ',') - 1)),
             stuff(rest1, 1, charindex(',', rest1 + ','), ''),
             stuff(rest2, 1, charindex(',', rest2 + ','), ''),
             lev + 1
      from cte
      where rest1 <> '' and rest2 <> ''
     )
select *
from cte
where x1 is not null;

Here is a db<>fiddle.

edited Sep 21 '19 at 20:14

answered Sep 21 '19 at 17:09

Gordon Linoff

1,242,037
58
646
786

Wow that's great, thanks. Never heard of a correlated subquery before. – Benny Sep 21 '19 at 17:23
What is the reason for "lev < 10"? – Benny Sep 21 '19 at 17:31
@Benny . . . (1) That is a recursive CTE. I can't believe my fingers typed something else. (2) The comparison on `lev` is just something I always put in when testing recursive CTEs. I removed it. – Gordon Linoff Sep 21 '19 at 20:15
Since `STRING_SPLIT()` needs v2016+ there is `OPENJSON` as a type-safe and position-safe string splitter. You might [read this (UPDATE sections)](https://stackoverflow.com/a/38275075/5089204) – Shnugo Sep 22 '19 at 17:03

score 0 · Answer 2 · answered Sep 22 '19 at 17:01

You've got an answer already, which is working fine, but this should be faster and easier:

You did not specify your SQL-Server's version, but - talking about STRING_SPLIT() - I assume it's at least v2016. If this is correct, you can use OPENJSON. Your list of numbers needs nothing more than brackets to be a JSON-array ([1,2,3]), while an array of words/letters can be transformed with some easy string operations (["a","b","c"]).

Following the docs, OPENJSON returns the elements position in [key], while the element itself is returned in [value]. You can simply JOIN these sets:

DECLARE @ParamList1 NVARCHAR(max) = '1,2,3,4,5';
DECLARE @ParamList2 NVARCHAR(max) = 'a,b,c,d,e';

SELECT p1.[key] AS FragmentNr
      ,p1.[value] AS P1
      ,p2.[value] AS P2
FROM OPENJSON(CONCAT('[',@ParamList1 + ']')) p1
INNER JOIN OPENJSON(CONCAT('["',REPLACE(@ParamList2,',','","'),'"]')) p2 ON p1.[key]=p2.[key] ;

In this answer you will find some details (UPDATE section 1 and 2).

Combine string_split column results in table SQL

2 Answers2