Multiple values in a single variable

Question

I have a script as this:

Declare @Ids varchar = '1,2,3';  
Select * from Table where Id in (@Ids)

How to separate or convert @Ids into comma separated integers ?

Note: I don't want to create a new function for this.

SQL does not implement an equivalent method to SPLIT found in most languages, you will have to create a function to do this. Like found here: http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql — JanR, Mar 09 '16 at 05:04
alternatively, rather than storing the `@Ids` in a varchar, create a temporary table with the `@Ids`, then you can just do a subquery — JanR, Mar 09 '16 at 05:05
@JanR: Yeah dats a good suggestion. But is there anything else than this. — Murtaza Munshi, Mar 09 '16 at 05:07
SQL is notoriously bad at dealing with comma separated lists, apart from creating a function to handle them, I wouldn't know of any clean options — JanR, Mar 09 '16 at 05:09

score 0 · Answer 1 · answered Mar 09 '16 at 09:56

It's not that pretty, and generally I put this in a function, but it works fine without one, if you already have a numbers table, you can skip the CTE, you can also get rid of the @delimiter variable if you are always going to use a comma if you like:

DECLARE @ids VARCHAR(MAX) = '1,2,3';
DECLARE @delimiter VARCHAR(MAX) = ',';
WITH    n AS ( SELECT   x = ROW_NUMBER() OVER ( ORDER BY s1.[object_id] )
               FROM     sys.all_objects AS s1
                        CROSS JOIN sys.all_objects AS s2
                        CROSS JOIN sys.all_objects AS s3
             )
    SELECT  *
    FROM    [table]
    WHERE   [Id] IN (
            SELECT  Item = SUBSTRING(@ids, x,
                                     CHARINDEX(@delimiter, @ids + @delimiter,
                                               x) - x)
            FROM    n
            WHERE   x <= CONVERT(INT, LEN(@ids))
                    AND SUBSTRING(@delimiter + @ids, x, LEN(@delimiter)) = @delimiter );

Multiple values in a single variable

1 Answers1