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!
Why/when is this better than this?:
SELECT *
FROM [AdventureWorks].[Person].[Contact]
WHERE FirstName LIKE ‘Gusta%’ OR FirstName LIKE ‘Cath%’
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.