0

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.

Community
  • 1
  • 1
JoeBrewing
  • 27
  • 7

1 Answers1

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)
Alex Kudryashev
  • 9,120
  • 3
  • 27
  • 36