sql server - How to Parse a comma delimited string of numbers into a temporary orderId table?

Question

I have a bunch of orderIds '1, 18, 1000, 77 ...' that I'm retreiving from a nvarchar(8000). I am trying to parse this string and put the id into a temporary table. Is there a simple and effective way to do this?

To view a list of all the orderIds that I parsed I should be able to do this:

select orderid from #temp

Answer 1

Give this a shot. It'll split and load your CSV values into a table variable.

declare @string nvarchar(500)
declare @pos int
declare @piece nvarchar(500)
declare @strings table(string nvarchar(512))

SELECT @string = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

if right(rtrim(@string),1) <> ','
   SELECT @string = @string  + ','

SELECT @pos =  patindex('%,%' , @string)
while @pos <> 0 
begin
 SELECT @piece = left(@string, (@pos-1))

 --you now have your string in @piece
 insert into @strings(string) values ( cast(@piece as nvarchar(512)))

 SELECT @string = stuff(@string, 1, @pos, '')
 SELECT @pos =  patindex('%,%' , @string)
end

SELECT * FROM @Strings

Found and modified from Raymond at CodeBetter.