Is there an easier way to combine multiple criteria using 'like'

Question

I was able to create my query by using Union. However I am wondering if there is an easier way to create the same results? I've tried using in ('abc%','def%') and like ('abc%','def%') however I get a syntax error near the comma separating the two words in parenthesis.

Here is what I have so far...

select distinct a.case,a.person,a.personid,b.plans,a.code,a.action,cast(a.datea as date) as datea,cast(a.dateb as date) as dateb,a.updatecode
from table(nolock) a

inner join table(nolock) b on a.column=b.column

where updatecode = 'invalid'

and code like ('pr%') and datea >= '2016-09-16' and len (b.plans) = 16

union

select distinct a.case,a.person,a.personid,b.plans,a.code,a.action,cast(a.datea as date) as datea,cast(a.dateb as date) as dateb,a.updatecode

from table(nolock) a

inner join table(nolock) b on a.column=b.column

where updatecode = 'invalid'

and code like ('sea%') and datea >= '2016-09-16' and len (b.plans) = 16

[Bad habits : Putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) — sstan, Sep 20 '16 at 12:49
`where updatecode = 'invalid' and (code like ('pr%') OR code like ('sea%')) and datea >= '2016-09-16' and len (b.plans) = 16` — artm, Sep 20 '16 at 12:49
Possible duplicate of [Combining "LIKE" and "IN" for SQL Server](http://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server) — Jesse Williams, Sep 20 '16 at 12:54

score 1 · Answer 1 · answered Sep 20 '16 at 12:49

1

You can use:

(code like 'pr%' OR code like 'sea%')

answered Sep 20 '16 at 12:49

Tab Alleman

31,483
7
36
52

score 1 · Answer 2 · answered Sep 20 '16 at 12:51

I don't believe there's a good, clean-looking solution, but you can string multiple like queries with or, as in:

 select * from config.devices where displayname like 'Im%' or displayname like 'In%'

This will return all results that start with either "Im" or "In".

score 1 · Answer 3 · answered Sep 20 '16 at 12:52

Try this:

select distinct a.case,a.person,a.personid,b.plans,a.code,a.action,cast(a.datea as date) as datea,cast(a.dateb as date) as dateb,a.updatecode
from table(nolock) a
inner join table(nolock) b on a.column=b.column
where updatecode = 'invalid'
and (code like ('pr%') OR code like ('sea%'))
and datea >= '2016-09-16' 
and len (b.plans) = 16

Is there an easier way to combine multiple criteria using 'like'

3 Answers3