Link to home
Start Free TrialLog in
Avatar of dkilby
dkilbyFlag for Canada

asked on

MS SQL Query + multiple like '%%'

i want to create a stored procedure that pulls a list from a table.

currently I am searching a comments field for keywords, using a like statement for each keyword, but I would like to pull the list of keywords from another table so if the keywords change i dont have to change the query.  Is this possible?

example of what i am currently doing.

select * from tblCustomerInfo
where comments like '%Test%' or comments like '%One%' or comments like '%Two%'
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Sure.  Load "Test", "One", etc. into a table and then  you can do this:

SELECT *
FROM tblCustomerInfo ci
WHERE
    EXISTS(SELECT 1 FROM otherTable o WHERE ci.comments LIKE o.string)
Avatar of dkilby

ASKER

it doesnt work, i put the keywords in a separate table and get nothing back, i test by doing this, and returned rows.

SELECT *
FROM tblCustomerInfo ci
WHERE
    EXISTS(SELECT 1 FROM KeyWords o WHERE ci.comments LIKE '%One%')
ASKER CERTIFIED SOLUTION
Avatar of Harish Varghese
Harish Varghese
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dkilby

ASKER

Thank you - thank worked
You need to put the %s in the strings in the table, NOT in the SQL SELECT.

Sorry, I should have stated:

Sure.  Load "%Test%", "%One%", etc. into a table and then do the EXISTS checks.

It's more flexible that way -- there may be times when you don't want leading and/or trailing %s.