Filtering with WHERE IN

I have a varchar(8000) variable that I store a list of integers.  They are comma separated.  When I feed these into a WHERE IN, such as:

select *
from table1
WHERE col1 IN (@mylist)

I get this error:

Conversion failed when converting the varchar value '6,7,8,9' to data type int

This list is built dynamically using something such as:

select @mylist = coalesce(@mylist + ',', '') + cast(numberCol1 as nvarchar)
from table1...

Any suggestions how I get around the error?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ephraim WangoyaCommented:

set @mylist = '(' + @mylist + ')'

select *
from table1
WHERE col1 IN @mylist
Ephraim WangoyaCommented:
Sorry, i must be tired today

Thats not correct
brettrAuthor Commented:
that gives

incorrect syntax near @mylist
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Ephraim WangoyaCommented:
Create a function to get the numbers form the list as below

Then run the query as

select *
from table1
where col1 IN (select ID from dbo.fnSplit(@mylist))
create FUNCTION [dbo].fnSplit(@LIST VARCHAR(MAX))
	DECLARE @nextpos INT
	DECLARE @valuelen INT
	SELECT @pos = 0, @nextpos = 1
	WHILE @nextpos > 0
		SELECT @nextpos = charindex(',', @LIST, @pos + 1)
		SELECT @valuelen = CASE 
					WHEN @nextpos > 0 THEN 
						len(@LIST) + 1
                    END - @pos - 1
		VALUES (substring(@LIST, @pos + 1, @valuelen))
      	SELECT @pos = @nextpos

Open in new window

brettrAuthor Commented:
Actually looking for something much simpler.
Ephraim WangoyaCommented:
You can also use charindex but I don' treally like the method

select *
from table1
where CHARINDEX(cast(col1 as varchar), @mylist, 1) > 0

brettrAuthor Commented:
Thanks.  I'll probably go with that one.  Looks like it is working fine.
Kevin CrossChief Technology OfficerCommented:
You may get some false positives with that solution.  Try checking COL1 = 1 against a list that have '11, 12, 13' for example.

If you are going to do it that way, I would ensure that you have a comma at the beginning and end of your list always and then search for your column value between 2 commas.
i.e., CHARINDEX(CONVERT(','+VARCHAR(10), col1)+',', ','+@mylist+',', 1)

Another approach if you really can't use a SPLIT function, would be to use dynamic SQL.

declare @sql as varchar(max);
set @sql = 'select * from table1 where col1 IN (' + @mylist + ')';

Hope that helps!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.