Need Help Converting Oracle Query to SQL Server

Question

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.

Alex Kudryashev · Accepted Answer · 2016-07-04 19:40:38Z

1

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)

answered Jul 4, 2016 at 19:40

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need Help Converting Oracle Query to SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related