How to use a set of strings in a WHERE statement of SQL?

Question

Sorry i am not sure how to titled the question well. I want to select few records in sql where a particular column is a set of strings.

Example . I have a table student and has columns ID and name. ID has records 1,2,3,4,5,6 . NAme has A,B,C,D,E,F.

I want to return C,D,E WHERE ID=[3,4,5].

I tried

SELECT FROM student WHERE ID=2,3,4

it gives error, ID=2,3,4 ='2,3,4' and it reads ID as a single columns. I am confused.

Also in my case, ID set are returned in a storedprocedure variable. that is like @ID

SELECT * FROM STUDENT WHERE ID=@ID

@ID above is a variable of a string type holding the set {1,2,3}. Please any help would be appreciated.

`WHERE ID=[3,4,5]` That's not valid syntax. You want `WHERE ID IN (3, 4, 5)`, and unfortunately you cannot add a single parameter containing the set of legal values there. Your best option is to either construct the SQL dynamically, or insert the values into a temporary table and do a join (or sub-select in the IN-clause). — Lasse V. Karlsen, Oct 21 '13 at 08:05

Raging Bull · Accepted Answer · 2014-03-01T09:56:56.950

5

Try this:

SELECT * FROM student WHERE ID IN (2,3,4)

Syntax:

test_expression IN 
    ( subquery | expression [ ,...n ]
    )

Read more about IN operator here.

edited Mar 01 '14 at 09:56

answered Oct 21 '13 at 08:05

Raging Bull

18,593
13
50
55

score 3 · Answer 2 · edited May 23 '17 at 12:14

3

WHERE ID=2,3,4 and WHERE ID='2,3,4' are invalid syntax for SQL.

Looks like you can use IN (Transact-SQL) on your situation.

Determines whether a specified value matches any value in a subquery or a list.

SELECT FROM student WHERE ID IN (2, 3, 4)

Also you might take a look Jeff's question Parameterize an SQL IN clause

edited May 23 '17 at 12:14

Community

1
1

answered Oct 21 '13 at 08:05

Soner Gönül

97,193
102
206
364

score 3 · Answer 3 · edited May 23 '17 at 11:51

3

If you are passing @ID as a variable with comma separated list of ids, WHERE IN (@ID) will not work.

I think best thing would be to use a Table Valued function to split them first and then query the table. Please check here for a Split() function.

Usage:

SELECT * FROM STUDENT 
WHERE ID IN (
              SELECT items FROM dbo.Split(@ID, ',') --Split function here
            )

edited May 23 '17 at 11:51

Community

1
1

answered Oct 21 '13 at 08:09

Kaf

33,101
7
58
78

It is a simple one what is the use of Functions here Please check this out "Select Username from Student where ID IN ' + '('+ @Id +')'" where @Id=2,3,4 we can use like this – Anjali Oct 21 '13 at 08:17
1

@Anjali: OP is using a stored procedure and not passing inline sql so It won't work unless you run a dynamic query. – Kaf Oct 21 '13 at 08:31
Ok, but it work for stored procedures also i am using that query in stored procedures – Anjali Oct 21 '13 at 08:42
Can you help me based on this. please come to chat – Anjali Oct 21 '13 at 09:23
@Anjali: sure no problem, you can invite me. – Kaf Oct 21 '13 at 09:28
i don't know how to Invite to chat – Anjali Oct 21 '13 at 09:30
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/39629/discussion-between-kaf-and-anjali) – Kaf Oct 21 '13 at 09:34

score 2 · Answer 4 · answered Oct 21 '13 at 08:08

If you want to fitler multiple values in Select, you should use "in ()":

SELECT * FROM student WHERE ID in (2,3,4)
OR
SELECT * FROM student WHERE ID between 2 and 4
OR
SELECT * FROM student WHERE ID = 2  OR ID = 3 OR ID = 4

In this case take the first one. The last one is very slow and not recommended in this scenario.

tomsullivan1989 · Answer 5 · 2013-10-21T10:39:15.380

2

Based on your comment below, you don't want to convert ID to an int. Instead, use LIKE to compare:

SELECT * from STUDENT
WHERE ', '+@ID+', ' LIKE ', '+CAST(ID as NVARCHAR(255)) + ', ';

However, the query will not be indexed. If you want the query to be indexed, then use dynamic SQL:

DECLARE @query NVARCHAR(max) = 'SELECT * FROM STUDENT WHERE ID IN ('+ @ID +')';
EXEC sp_executesql @query;

edited Oct 21 '13 at 10:39

answered Oct 21 '13 at 08:11

tomsullivan1989

2,760
14
21

i understand the solution given should resolve my question but my ID column is of bigINT datatype and my `@id` variable is returned as string like {1,2,3} when i try conactenating the two i mean like SELECT * FROM students where ID IN @ID i get error because column ID is of BIGINT and i can't convert `@ID` to INT pls how do i go about this? You there? – Nuru Salihu Oct 21 '13 at 10:18

score 2 · Answer 6 · answered Oct 21 '13 at 08:13

2

Please check this out

Select * from Student where Id IN ('2','3','4')

and check this out

Select Username from Student where ID IN ' + '('+ @Id +')'
 where @Id=2,3,4

answered Oct 21 '13 at 08:13

Anjali

1,680
4
26
48

i understand the solution given should resolve my question but my ID column is of bigINT datatype and my `@id` variable is returned as string like {1,2,3} when i try concatenating the two i mean like SELECT * FROM students where ID IN @ID i get error because column ID is of BIGINT and i can't convert `@ID` to INT pls how do i go about this? You there? – Nuru Salihu Oct 21 '13 at 10:19

Deepak Bhatia · Answer 7 · 2013-10-21T10:44:06.840

Since you are using Stored Procedure, that also has only equality compare i.e. id = 1, so either you have too execute three queries by splitting the input by comma separated values.

OR you can add a new procedure with a custom function to server with the SQL

CREATE FUNCTION dbo.myparameter_to_list (@parameter VARCHAR(500)) returns @myOutput TABLE (mytempVal VARCHAR(40))
AS  
begin
     DECLARE @TempTable table
          (
          mytempVal VARCHAR(40)
          )

     DECLARE @MySplittedValue varchar(40), @PositionOfComma int

     SET @par = LTRIM(RTRIM(@parameter))+ ','
     SET @PositionOfComma = CHARINDEX(',', @parameter, 1)

     IF REPLACE(@parameter, ',', '') <> ''
     BEGIN
          WHILE @PositionOfComma > 0
          BEGIN
               SET @MySplittedValue = LTRIM(RTRIM(LEFT(@par, @PositionOfComma - 1)))
               IF @MySplittedValue <> ''
               BEGIN
                    INSERT INTO @TempTable (mytempVal) VALUES (@MySplittedValue) --Use conversion if needed
               END
               SET @par = RIGHT(@par, LEN(@par) - @PositionOfComma)
               SET @PositionOfComma = CHARINDEX(',', @par, 1)

          END
     END    
     INSERT @myOutput
     SELECT mytempVal 
        FROM @TempTable
     RETURN
END

In your stored procedure you would use it like this:

Create Procedure StudentSelectFromSet 
@Ids VARCHAR(MAX)
AS
SELECT * FROM student Stud
WHERE Stud.Id IN(SELECT value FROM dbo.myparameter_to_list (@Ids))

and then execute this new procedure as you were accessing earlier.

i understand the solution given should resolve my question but my ID column is of bigINT datatype and my `@id` variable is returned as string like {1,2,3} when i try concatenating the two i mean like SELECT * FROM students where ID IN @ID i get error because column ID is of BIGINT and i can't convert `@ID` to INT pls how do i go about this? You there? — Nuru Salihu, Oct 21 '13 at 10:20
@NuruSalihu hope new edited answer provides necessary solution to your query — Deepak Bhatia, Oct 21 '13 at 10:32

score 2 · Answer 8 · edited Oct 21 '13 at 09:06

2

Select * from Student where Id='2'
union all
Select * from Student where Id='3'
union all
Select * from Student where Id='4'

edited Oct 21 '13 at 09:06

Nazik

8,696
27
77
123

answered Oct 21 '13 at 08:41

pretty_cool

21
1

How to use a set of strings in a WHERE statement of SQL?

8 Answers8