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

Solution 1

The fastest way via a numbers table that takes 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 I've been using for years based on the following article: http://www.sqlservercentral.com/articles/T-SQL/62867/

Solution 2

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.

Solution 3

what do you think about this one?

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

Solution 4

Here is a UserDefined function that returns a DataTable with Id which you can use for your joins. Look at this article which addresses 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

Solution 5

One-query 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

View more solutions

Comments

[Taryn]

While this may answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.

[SGS]

I know this is old now but in case anybody intends on using it, it doesn't output the last number in a list if it is less than 10. eg. '10,1' returns 10 but '10,11' returns 10 and 11 as expected

[Johnny_D]

Afaik this depends on numbers range that you specify, that's why it isn't that flexible.

[Makotosan]

I realize this was answered a few years ago but in case someone else finds this page, I would discourage the use of generating dynamic SQL as it could be the source of a security vulnerability. If the input string comes from an untrusted source (such as user input), a clever user could perform a SQL injection attack.

[Dan Esparza]

Shouty, but beautifully simple. Thanks!