CODEWITHSUNDEEP
sql-server
JoeBrewing
JoeBrewing

Reputation: 27

Need Help Converting Oracle Query to SQL Server

Several weeks ago I made a post to get help with converting a comma delimited list of values into a format that it could be used as part of an IN clause in Oracle. Here is a link to the post.

Oracle invalid number in clause

The answer was to split up the list into an individual row for each value. Here's the answer that I ended up using.

SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
    FROM ( SELECT '1,2,3,4' str FROM dual )
    CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0

Is there a way that I can do something similar in SQL Server without having to create a custom function? I noticed that there's a STRING_SPLIT function, but I don't seem to have access to that on this SQL Server.

Any advice you might have would be greatly appreciated. I've been trying to take a stab at this for the majority of the day.

Upvotes: 0

Views: 37

Answers (1)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

String_split function is available in MS SQL Server starting from version 2016. If you use older version you can write a few lines of code which do the same.

declare @str varchar(100)='1,2,3,4' --initial string
;with cte as (--build xml from the string
select cast('<s>'+replace(@str,',','</s><s>')+'</s>' as xml) x
)
--receive rows
select t.v.value('.[1]','int') value
from cte cross apply cte.x.nodes('s') t(v)

Upvotes: 1

Related Questions