Prome Nabid Prome Nabid - 1 month ago 8
SQL Question

Merge two STRING_SPLIT

Consider the problem below:
I have two strings for split:

STR1 = 'b;a;c;d;e'

STR2 = '3;1;4;2;5'


I want to split and merge these two strings based on their index, such that the result is:

b -> 3
a -> 1
c -> 4
d -> 2
e -> 5


I tried with
STRING_SPLIT
, but
order by
sorts them all.

SELECT A.VALUE, B.VALUE FROM (
SELECT VALUE, ROW_NUMBER() OVER(ORDER BY VALUE) AS RW
FROM STRING_SPLIT('b;a;c;d;e', ';')
) A
INNER JOIN (
SELECT VALUE, ROW_NUMBER() OVER(ORDER BY VALUE) AS RW
FROM STRING_SPLIT('3;1;4;2;5', ';')
) B
ON A.RW = B.RW


This produces the following result:

a 1
b 2
c 3
d 4
e 5

Answer Source

Perhaps something like this?

declare @STR1 varchar(64) = 'b;a;c;d;e'

declare @STR2 varchar(64)  = '3;1;4;2;5'

;with cte as(
select
    value
    ,RN = row_number() over (order by (select null))
from
    STRING_SPLIT(@STR1,';')),

cte2 as(
select
    value
    ,RN = row_number() over (order by (select null))
from
    STRING_SPLIT(@STR2,';'))

select
    c.value + d.value
from
    cte c
    inner join 
    cte2 d on c.RN = d.RN