CODEWITHSUNDEEP
sqlsql-server
Aflred
Aflred

Reputation: 4593

use % wildcard with ' in ' operator in sql server

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.

Upvotes: 3

Views: 4020

Answers (2)

vittore
vittore

Reputation: 17589

Replace IN with a JOIN ON 

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

Upvotes: 1

usr
usr

Reputation: 171226

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.

Upvotes: 3

Related Questions