Benny
Benny

Reputation: 226

Combine string_split column results in table SQL

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?

Upvotes: 0

Views: 182

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270331

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.

Upvotes: 2

Related Questions