T-SQL: Select WHERE IN varchar

Posted on 2008-11-12
Medium Priority
Last Modified: 2012-05-05
Can some one tell me how to use the IN keyword to select rows from a table whose one column's value is already defined in an array? Here is my situation: provided a 'array' let say 'value1,value2,value3' I would like to do the following:
declare @values varchar(max)
set @values = 'value1,value2,value3' 
select * from table1 where colum1 in @values

Open in new window

Question by:karakav
LVL 15

Expert Comment

by:Haris Djulic
ID: 22947728
i had similar issue and this is how i solved it ...

CREATE TABLE  #upit( value varchar(2) )
insert into #upit  values ( '01' )
insert into #upit  values ( '02' )
select * from table1 where colum1 in (select value from #temp)

Open in new window

LVL 14

Expert Comment

ID: 22947746
How about this....
declare @values varchar(max)
declare @select nvarchar(1024)
set @values = 'value1,value2,value3' 
SET @select = 'select * from table1 where colum1 in (' + @values + ')'
EXEC sp_executesql @select

Open in new window

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22947973
please create the function dbo.ParmsToList as shown in the last comment here:

and then, use it like this:

declare @values varchar(max)
set @values = 'value1,value2,value3' 
select * from table1 where colum1 in (select value from dbo.ParmsToList(@values,','))

Open in new window


Author Comment

ID: 22948057
puranik_p, your solution is not working as values in @values are considered columns.

Author Closing Comment

ID: 31516281
Thanks a lot.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question