Help on apply string_split to multiple columns

Question

I need to apply string_split to multiple columns.

I found this post:https://learn.microsoft.com/en-us/answers/questions/1100898/can-we-apply-string-split-function-on-multiple-col.

Tried this sample code, returning 18 rows. But I only want 3 rows for each id.

Declare @tbl table (id int,val1 varchar(100),val2 varchar(100))
insert into @tbl values
(1,'11,22,33','aa;bb;cc'),(2,'44,55,66','dd;ee;ff')

;with cte as
(
select id,c1.value as val1,val2 
from @tbl
cross apply string_split(val1,',')c1
)
select id,val1,c2.value as val2 from cte
cross apply string_split(val2,';')c2

Please help. Thanks in advance.

Answer 1

Hi @Siver

Please check this query:

;WITH CTE AS 
(
 SELECT id
       ,s1 = '["' + REPLACE(val1, ',', '","') + '"]'
       ,s2 = '["' + REPLACE(val2, ';', '","') + '"]'
 FROM @tbl
)
SELECT id, VAL1.[value] AS val1, VAL2.[value] AS val2
FROM CTE
     CROSS APPLY OPENJSON (s1, N'$') AS VAL1
     CROSS APPLY OPENJSON (s2, N'$') AS VAL2
WHERE VAL1.[key] = VAL2.[key];

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

In modern SQL, try this:

select id, c1.value as val1, c2.value as val2 
from @tbl
cross apply string_split(val1, ',', 1) c1
cross apply string_split(val2, ';', 1) c2
where c1.ordinal = c2.ordinal