Multiple LIKE clauses in a single WHERE statement

I recently came up against a scenario where, in amongst a number of other filters, I had to deal with a couple of wildcard criteria. As there is no option to have multiple LIKE clauses in native SQL, such as WHERE Field LIKE IN (‘%Option1%’,’%Option2%’), I feared I was stuck with having to duplicate my WHERE clause in its entirety for each LIKE operation – until I found this cunning bit of code (from the thread here):

/* Apply Multiple LIKE clauses in a single WHERE clause */
SELECT *
 
FROM [AdventureWorks].[Person].[Contact]
 
WHERE CASE
  WHEN FirstName LIKE ‘Gusta%’ THEN 1
  WHEN FirstName LIKE ‘Cath%’ THEN 1
  END = 1

An elegant solution!

2 thoughts on “Multiple LIKE clauses in a single WHERE statement

  1. Why/when is this better than this?:

    SELECT *
    FROM [AdventureWorks].[Person].[Contact]
    WHERE FirstName LIKE ‘Gusta%’ OR FirstName LIKE ‘Cath%’

  2. This requirement came from some table generated SQL that I had to knock up, where each clause was on a seperate row of a table, and each clause may not be a LIKE – it may be an AND or a NOT EQUALS. Wrapping it up in case statement is not a world apart from just enclosing it brackets. I just liked this better.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>