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?
brettrAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief 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 + ')';
exec(@sql);

Hope that helps!

0
 
Ephraim WangoyaCommented:

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

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

Thats not correct
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
brettrAuthor Commented:
that gives

incorrect syntax near @mylist
0
 
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))
RETURNS 
	@IDS TABLE(ID INTEGER)
AS
BEGIN	
	DECLARE @pos INT
	DECLARE @nextpos INT
	DECLARE @valuelen INT
		
	SELECT @pos = 0, @nextpos = 1
	WHILE @nextpos > 0
	BEGIN
		SELECT @nextpos = charindex(',', @LIST, @pos + 1)
		SELECT @valuelen = CASE 
					WHEN @nextpos > 0 THEN 
						@nextpos
                              		ELSE 
						len(@LIST) + 1
                    END - @pos - 1
		INSERT @IDS 
		VALUES (substring(@LIST, @pos + 1, @valuelen))
      	SELECT @pos = @nextpos
	END
	
	
	RETURN 
END

Open in new window

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

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

0
 
brettrAuthor Commented:
Thanks.  I'll probably go with that one.  Looks like it is working fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.