Passing a varchar full of comma delimited values to a SQL Server IN function

Question

Duplicate of
Dynamic SQL Comma Delimited Value Query
Parameterized Queries with Like and In

I have a SQL Server Stored Procedure where I would like to pass a varchar full of comma delimited values to an IN function. For example:

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * 
FROM sometable 
WHERE tableid IN (@Ids);

This does not work of course. I get the error:

Conversion failed when converting the varchar value '1,2,3,5,4,6,7,98,234' to data type int.

How can I accomplish this (or something relatively similar) without resorting to building dynamic SQL?

Solution 1

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)

Solution 2

Of course if you're lazy like me, you could just do this:

Declare @Ids varchar(50) Set @Ids = ',1,2,3,5,4,6,7,98,234,'

Select * from sometable
 where Charindex(','+cast(tableid as varchar(8000))+',', @Ids) > 0

Solution 3

No Table No Function No Loop

Building on the idea of parsing your list into a table our DBA suggested using XML.

Declare @Ids varchar(50)
Set @Ids = ‘1,2,3,5,4,6,7,98,234’

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

SELECT * 
FROM
    SomeTable 
    INNER JOIN @XML.nodes('i') x(i) 
        ON  SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')

These seems to have the same performance as @KM's answer but, I think, a lot simpler.

Solution 4

You can create a function that returns a table.

so your statement would be something like

select * from someable 
 join Splitfunction(@ids) as splits on sometable.id = splits.id

Here is a simular function.

CREATE FUNCTION [dbo].[FUNC_SplitOrderIDs]
(
    @OrderList varchar(500)
)
RETURNS 
@ParsedList table
(
    OrderID int
)
AS
BEGIN
    DECLARE @OrderID varchar(10), @Pos int

    SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
    SET @Pos = CHARINDEX(',', @OrderList, 1)

    IF REPLACE(@OrderList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
            IF @OrderID <> ''
            BEGIN
                INSERT INTO @ParsedList (OrderID) 
                VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
            END
            SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
            SET @Pos = CHARINDEX(',', @OrderList, 1)

        END
    END 
    RETURN
END

Solution 5

It's a very common question. Canned answer, several nice techniques:

http://www.sommarskog.se/arrays-in-sql-2005.html

View more solutions

Comments

[beach]

Not wise.... try this: SET @id = '0); SELECT ''Hi, I just hosed your server...''--'

[KM.]

This looping will be slow, you do not need to loop to split a string in SQL, see my answer for an example of how...

[Charles Bretana]

What do you thik the Query proceser is doing, when you execute your Select statement? - generating all the rows instantaneously using trans-temporal quantumn physics? It's also looping... You are just changing from a loop you explicitly control, to one the SQL Server Query processer controls...

[John Smith]

ahh, injection. But this usually only applies when a user is allowed to input.

[KM.]

@Charles Bretana, Ha! You can write code 10 different ways, and each will perform differently (speed wise). The goal is to write it the way that will run the fastest. Just try it out, run this split method against the stored procedure looping method listed in another question. Run each 100 times, and see how long they take. ----- FYI, I'm sure the SQL Server internal looping is MUCH faster and better optimized than a user created stored procedure, with local variables and a WHILE loop!

[Will Rickards]

Do you have a solution for more than 8000 characters? A few of the places I've needed this have hit the 8000 character limitation so I wrote the implementation I linked above.

[KM.]

@Will Rickards, if you need to handle strings >8k, you could make your loop faster by using a CLR (sommarskog.se/arrays-in-sql.html) or change your loop to process chunks of 8k (make sure you break on commas), but pass those chunks into a function like mine.

[Alexandre Leites]

That could be one of the reasons you would love RDBMS with first class array support fxjr.blogspot.com/2009/05/… Integrating CLR to MSSQL to implement multiple values for IN, vendor lock-in: sommarskog.se/arrays-in-sql-2005.html

[CeejeeB]

I used this approach and it worked fine until I deployed to our live server which has 4.5 million rows at which point it was far too slow. Always consider scalability!

[João Paladini]

@CeejeeB Already considered. Note the word "lazy", when I care about performance, scalability, maintenance or supportability, I do it similar to KM.'s answer. I.E., the right way.

[Walter Mitty]

Charles and KM. There is some merit in each of your comments. Yes, the SQL engine will, at some point, loop through the individual numbers. But the engine's loop will likely run much faster than a user written loop. The real solution, to avoid looping in the first place is to redesign the schema to comply with first normal form. The CSV field looks like 1NF, but it isn't really 1NF. That's the real problem.

[Jason Ebersey]

Brilliant! Thank-you so much for this. I've been battling with an efficient way to do this for ages! Well done!

[DaveD]

The linked page really has some great info, especially if you want to down the CLR route.

[Albert Laure]

this is what other people have told me to use.. can you please explain the INNER JOIN @XML.nodes('i') x(i) ON SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)') part to me? sorry im very new to this.

[T_D]

@RBarryYoung That's a nice creative solution, I did thumbed it up. Althought I never like seeing CharIndex(..)>0, the most semantic and readable alternative I can come up with would be using LIKE to know whether it contains the string =) Cheers!

[Peter PitLock]

Is there a way to just split the @xml without joining to the other table? e.g. select @xml.nodes(i) and it will return rows for each of 1,2,3,5,4 etc

[T. Sar]

Your answer have some broken links... can you check them out?

[Will Rickards]

added code as requested though I'm not sure I use this algorithm anymore. I switched to passing xml and then using sql's xml support some time ago.

[gknicker]

Brilliant. I added a cast to int on the CTE id for joining to my table's unique identifier.

[Morvael]

@PeterPitLock - Yes, See my answer below. You can just use xml as if it were any other table

[Hans]

The reason is that using a function in a where statement will make the statement non-sargable meaning that it will result in a scan.

[João Paladini]

@Hans That's not entirely true, there are some (few) functions and cases that are sargable (LEFT(..) on an indexed [N]VARCHAR column being the most common example). However, most are not, and it is true that this one certainly is not.

[Matt]

Does not work for me. Tried it with Northwind's Categories table using CategoryID and I got was the error: Error 493: The column 'i' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

[Jaxidian]

This poor-man's way of doing this is exactly what I was looking for. I didn't want to create a custom function (because reasons) and I'm only dealing with generating an in-memory set of days in a year (365-366 records in memory) to populate a configuration table once a year. This is perfect! (Yes, I know this is a very old answer but still, thanks!)

[Alicia]

This is a very succinct and performant way of doing it. This is my preferred answer .

[Robb Sadler]

I guess I was a little thick looking at the answer you commented on, but had trouble turning that into an IN clause. Using this example helped. Thanks!

[Matt]

@Matt I got that too. Try replacing SELECT * with SELECT SomeTable.* and it should work.

[Matt]

@Matt - I tried that, but then I am getting a different error: Error 207: Invalid column name 'Id'.

[Jeff Mergler]

Security aside, use of concatenated literals is also not a great idea from a performance standpoint: the concatenated literals will create duplicate query plans in the query plan cache each time the SQL statement is executed with a different value in @id. If this is a busy server, say 'hola' to query plan cache bloat (ref. mssqltips.com/sqlservertip/2681/…)

[stevenferrer]

could you explain a little?

[user1400290]

It's clear that like operator is used to filter records I generally use this in such scenario for a long time. It's really simple & easy to understand.

[Andrew]

This is the same approach posted in 2009 here.

[Andrew]

This worked perfectly for me: I could query the original list with: SELECT x.i.value('.', 'varchar(max)') FROM @XML.nodes x('i')

[Vasiliy Zverev]

MS SQL (T-SQL) doesn't have FIND_IN_SET()

[BVernon]

This is great for me because I've got a scenario where I don't want to add any new functions to the database and I'm working on an older version that doesn't support STRING_SPLIT.

[Hong Van Vit]

it is not work when id > 10, for example DECLARE @Ids NVARCHAR(1000) = '3,4,5,6,7,8,9,10,11,12,'. it get all 1,2 & 11, 12

[JeremyW]

Supported in SQL Server 2016 and above.