How can I use like in a list for SQL?

Question

I have a column in a table that I want to search using like. For example

SELECT * from Employee
WHERE Employee.LName like ('%Mac%', '%Smi%', '%Wal%')

When I try that, it doesn't work. I'd like to be able to do something like that, rather than like

SELECT * from Employee
WHERE Employee.LName like '%Mac%'
OR Employee.LName like '%Smi%'
OR Employee.LName like '%Wal%'

Answer 1

Try this:

create table #lookfor (LikeName varchar(32))
insert into #lookfor values ('%Mac%'),('%Smi%'),('%wal%')


select * 
from employee emp 
join #lookfor lf on emp.LastName like lf.Likename

• Build a temporary table to hold the expressions.
• Join your main table and #temp table using the LIKE operator