use % wildcard with ' in ' operator in sql server

Question

i have a stored procedure and im passing a list of comma separated values as the @At1

Create spGetProducts @At1 VARCHAR(200)
begin
select * from tblProducts where Category1 IN (SELECT * FROM CSVToTable(@At2))
end

the function CSVToTable basically takes the comma separated values and puts them in a table.

problem is i would like to use a LIKE wildcard with the IN operator however that does not work.

So if i have a in row Category1 a test and the CSVTOTABLE would return a value of 'es', then it would select the row just like if i had %es%. Basically i just want to have percentage signs in the comma separated values while using the in operator.

im using sql server 2012

EDIT

my CSVTABLE return one column table with each row having a comma separated value.

Don't use `*` in the IN clause, only exist clause can use that. You need to select a single value — Darren Kopp, Dec 31 '13 at 16:40
An advice avoid suing `sp` prefix for your stored Procedure names. read here for more information http://msdn.microsoft.com/en-us/library/dd172115(v=vs.100).aspx — M.Ali, Dec 31 '13 at 16:42
@M.Ali - [There is no underscore there](http://stackoverflow.com/questions/20530211/avoid-naming-user-stored-procedures-sp-or-sp/20530262#20530262) — Martin Smith, Dec 31 '13 at 16:58
@MartinSmith opss I thought it was true for both `sp_` and `sp`. but it would be nice to avoid it all together. I personally use `usp`. — M.Ali, Dec 31 '13 at 17:03

score 3 · Answer 1 · answered Dec 31 '13 at 16:40

Rewrite the IN to EXISTS which is more general:

select *
from tblProducts p
where exists (
 select *
 from CSVToTable(@At2)
 where Category1 LIKE (SomeExpressionInvolvingTheResultFromCSV)
)

You might want to pull the results from CSVToTable into a table variable or temp table first for caching reasons.

score 1 · Accepted Answer · answered Dec 31 '13 at 16:40

1

Replace IN with a JOIN ON

  select distinct t.* from tblProducts t 
     inner join CSVToTable(@At2) f  
        on t.category1 like f.field1

answered Dec 31 '13 at 16:40

vittore

17,449
6
44
82

This might duplicate rows in case of duplicate matches. – usr Dec 31 '13 at 16:41
@usr The just add `distinct` – vittore Dec 31 '13 at 16:42
And what do you do when you don't want distinct results? This is a hack plastered over an underlying deficiency. This is also a potential perf problem. – usr Dec 31 '13 at 19:27

use % wildcard with ' in ' operator in sql server

2 Answers2