I have two SQL variables @item1
and @item2
, which have values "abc,def"
, "ghi,jkl"
respectively.
And I have a table like this:
--------------------
Id | item1 | item2 |
--------------------
The result I want is:
--------------------
Id | item1 | item2 |
--------------------
1 | abc | ghi |
--------------------
2 | def | jkl |
--------------------
I have tried to use the splitstring
function in SQL Server, but thats not working in this scenario.
What I have tried
Insert into table (item1,item2)
(Select * from Splitstring (@item1,',')),
(Select * from Splitstring (@item2, ','))
CodePudding user response:
Starting from SQL Server 2016, a JSON-based approach is a possible option. The idea is to transform the input text values into a valid JSON array (abc,def
into ["abc","def"]
) and parse this array with OPENJSON()
. The result is a table with columns key
, value
and type
and the key
column holds the 0-based index of each item from the parsed array.
DECLARE @item1 varchar(max) = 'abc,def'
DECLARE @item2 varchar(max) = 'ghi,jkl'
INSERT INTO [Table] (item1, item2)
SELECT j1.[value], j2.[value]
FROM OPENJSON(CONCAT('["', REPLACE(@item1, ',', '","'), '"]')) j1
FULL OUTER JOIN OPENJSON(CONCAT('["', REPLACE(@item2, ',', '","'), '"]')) j2
ON j1.[key] = j2.[key]
Note, that starting from SQL Server 2022, you may use SRING_SPLIT()
with the enable_ordinal
parameter to get the same results:
INSERT INTO [Table] (item1, item2)
SELECT s1.[value], s2.[value]
FROM STRING_SPLIT(@item1, ',', 1) s1
FULL OUTER JOIN STRING_SPLIT(@item1, ',', 1) s2
ON s1.[ordinal] = s2.[ordinal]