Merge two STRING_SPLIT

user_3731282 0
Viewed 1765

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
sql-server-2016 tsql sql-server sql
4 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
user_61678550

STRING_SPLIT does not return a row number. It would not work for your requirement.

Here i am using Jeff's DelimitedSplit8K http://www.sqlservercentral.com/articles/Tally+Table/72993/ 

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

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

select  s1.Item , s2.Item
from    DelimitedSplit8K (@STR1, ';') s1
    inner join DelimitedSplit8K (@STR2, ';') s2 on  s1.ItemNumber   = s2.ItemNumber
order by s1.ItemNumber
user_1856470

I like the solution that you tried. It works if you order by a literal value instead of VALUE:

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

Another way to do it without depending on STRING_SPLIT() function:

DECLARE @xml       AS XML, 
        @xml2      AS XML, 
        @str       AS VARCHAR(100), 
        @str2      AS VARCHAR(100), 
        @delimiter AS VARCHAR(10) 

SET @str='b;a;c;d;e' 
SET @str2 = '3;1;4;2;5' 
SET @delimiter =';' 
SET @xml = Cast(( '<X>' + Replace(@str, @delimiter, '</X><X>') 
                  + '</X>' ) AS XML) 
SET @xml2 = Cast(( '<X>' + Replace(@str2, @delimiter, '</X><X>') 
                   + '</X>' ) AS XML) 

SELECT a.value, 
       b.value 
FROM   (SELECT n.value('.', 'varchar(10)') AS value, 
               Row_number() 
                 OVER ( 
                   ORDER BY (SELECT 100))  AS SNO 
        FROM   @xml.nodes('X') AS T(n))a 
       INNER JOIN (SELECT n.value('.', 'varchar(10)') AS value, 
                          Row_number() 
                            OVER ( 
                              ORDER BY (SELECT 100))  AS SNO 
                   FROM   @xml2.nodes('X') AS T(n))b 
               ON a.sno = b.sno
user_19104420
Related