Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 683
nim_10 Avatar asked Oct 17 '25 09:10

nim_10


1 Answers

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
like image 186
S3S Avatar answered Oct 19 '25 23:10

S3S



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!