How to parse a comma-delimited string of numbers into a temporary table orderId?

Question

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


To view a list of all the orderIds parameters that I have analyzed, I should be able to do this:

  select orderid from #temp 

Answer 1

The fastest way through a table of numbers, which takes a few seconds to create:

--First build your numbers table via cross joins select top 8000 ID=IDENTITY(int,1,1) into numbers from syscolumns s1 ,syscolumns s2 ,syscolumns s3 GO --add PK alter table numbers add constraint PK_numbers primary key clustered(ID); GO create table #temp( ID int identity(1,1) primary key, StringValues varchar(8000) ) declare @orderIds varchar(8000) set @orderIds = ',1, 18, 1000, 77, 99, 1000, 2, 4,' insert into #temp(StringValues) select substring(@orderIds,ID+1,charindex(',',@orderIds,ID+1)-ID-1) from numbers where ID < len(@orderIds)and substring(@orderIds,ID,1) = ','; 

This is a great method that I have been using for many years based on the following article: http://www.sqlservercentral.com/articles/T-SQL/62867/

Answer 2

Take a picture. It will 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 to CodeBetter .

Answer 3

What do you think about this?

 CREATE TABLE #t (UserName VARCHAR(50)) DECLARE @sql VARCHAR(MAX) SELECT @sql = 'INSERT INTO #t SELECT ''' + REPLACE(@UserList, ',', ''' UNION SELECT ''') + '''' PRINT (@sql) EXEC (@sql) SELECT * FROM #t IF OBJECT_ID('tempdb..#t') IS NOT NULL BEGIN DROP TABLE #t END 

http://swooshcode.blogspot.ro/2009/10/sql-split.html

Answer 4

Here is a UserDefined function that returns a DataTable with an identifier that you can use for your connections. Take a look at this article , which reviews this case quite well.

  CREATE FUNCTION ParamParserFn( @delimString varchar(255), @delim char(1)) RETURNS @paramtable TABLE ( Id int ) AS BEGIN DECLARE @len int, @index int, @nextindex int SET @len = DATALENGTH(@delimString) SET @index = 0 SET @nextindex = 0 WHILE (@len > @index ) BEGIN SET @nextindex = CHARINDEX(@delim, @delimString, @index) if (@nextindex = 0 ) SET @nextindex = @len + 2 INSERT @paramtable SELECT SUBSTRING( @delimString, @index, @nextindex - @index ) SET @index = @nextindex + 1 END RETURN END GO 

Answer 5

Change the value> na> = in the following code to work correctly only on inputs with a single comma. Subscribe to the first comment in a related article.

 WHILE (@len > @index ) BEGIN SET @nextindex = CHARINDEX(@delim, @delimString, @index) 

Answer 6

Single request solution:

 WITH T(LST) AS ( SELECT CAST(N'4,8,15,16,23,42' AS NVARCHAR(MAX)) ), T2(V, REST) AS ( SELECT LEFT(LST+',', CHARINDEX(',', LST+',')-1), SUBSTRING(LST+',', CHARINDEX(',', LST+',')+1, LEN(LST+',')) FROM T UNION ALL SELECT LEFT(REST, CHARINDEX(',', REST)-1), SUBSTRING(REST, CHARINDEX(',', REST)+1, LEN(REST)) FROM T2 WHERE CHARINDEX(',', REST) > 1 ) SELECT V FROM T2