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%'

Is your list of potential matches arbitrarily long? Is it dynamic? In either your "default" solution or @Sparky's with the temp table may be faster with PATINDEX then like. — Karl Kieninger
– Karl Kieninger, Commented Mar 7, 2014 at 22:27
The list of matches is going to be hardcoded in (and probably added to a little bit over time). I've never heard of PATINDEX. — Amanda_Panda
– Amanda_Panda, Commented Mar 7, 2014 at 22:30
Definitely look up PATINDEX (and CHARINDEX for that matter). One discussion of LIKE vs PATINDEX can be found stackoverflow.com/questions/8052425. — Karl Kieninger
– Karl Kieninger, Commented Mar 8, 2014 at 0:37

paparazzo · Accepted Answer · 2014-03-07 23:27:32Z

2

I don't know if this is easier but it is an option
It is nice if you need to mock up a join

  SELECT [docEnum1].[value]
    FROM [docEnum1]
    join ( values ('%new%'), ('%allen%'), ('%waste%')
         ) as [joinVals] (val)
      on [docEnum1].[value] like [joinVals].[val]

edited Mar 7, 2014 at 23:27

answered Mar 7, 2014 at 23:20

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amanda_Panda Over a year ago

Not really that different from the below answer, right?

paparazzo Over a year ago

You mean the above accepted solution? One it does not create a #temp table? Really you think a solution with more code and #temp is simpler? What if #lookfor is in use?

Sparky · Accepted Answer · 2014-03-07 22:23:59Z

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

edited Mar 7, 2014 at 22:23

answered Mar 7, 2014 at 22:18

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

1 Comment

Amanda_Panda Over a year ago

I've been really busy at work so haven't had time to try this yet, but I really like the simplicity of this solution.

Helge Telefonmann · Accepted Answer · 2024-02-14 15:13:58Z

0

On SQL Server, you can do just the same w/o temp tables, but with the same simplicity, using a table variable:

DECLARE @lookfor TABLE (LikeName VARCHAR(50))
INSERT INTO @lookfor(LikeName) VALUES ('%Mac%'),('%Smi%'),('%wal%');

SELECT * 
FROM employee emp 
JOIN @lookfor lf ON emp.LastName LIKE lf.Likename

answered Feb 14, 2024 at 15:13

Helge Telefonmann

394 bronze badges

Collectives™ on Stack Overflow

How can I use like in a list for SQL?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related