CODEWITHSUNDEEP
c#sql-serversql-server-2008select
Nuru Salihu
Nuru Salihu

Reputation: 4928

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

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.

Upvotes: 1

Views: 4717

Answers (8)

Raging Bull
Raging Bull

Reputation: 18747

Try this:

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

Syntax:

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

Read more about IN operator here.

Upvotes: 7

Deepak Bhatia
Deepak Bhatia

Reputation: 6276

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.

Upvotes: 2

tomsullivan1989
tomsullivan1989

Reputation: 2780

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;

Upvotes: 2

pretty_cool
pretty_cool

Reputation: 21

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

Upvotes: 2

Anjali
Anjali

Reputation: 1718

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

Upvotes: 2

Kaf
Kaf

Reputation: 33829

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
            )

Upvotes: 3

Dannydust
Dannydust

Reputation: 773

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.

Upvotes: 2

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98750

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

Upvotes: 3

Related Questions