SQL Workaround for Using Like in an In Statement

The like function is quite handy to use in SQL; however, it cannot be used in an “in” statement.  The “in” statement is effectively using “OR =”, and there is no equivalent that uses and “OR LIKE”.

For example, lets say you have a list of names, and you want to see all last names that start with vowels.  You can write it like this:

SELECT LastName FROM NameList

WHERE LastName LIKE ‘a%’ OR LastName LIKE ‘e%’ OR LastName LIKE ‘i%’ or LastName LIKE ‘o%’ or LastName LIKE ‘u%’

 

This would make it much easier to read, but it doesn’t work.

SELECT LastName FROM NameList

WHERE LastName IN (‘a%’,’e%’,’i%’,’o%’,’u%’)

You can workaround this limitation by using an inner join of select with the union statement

SELECT n.LastName
FROM NameList AS n
INNER JOIN
(
SELECT 'a%' FilterMatch
UNION SELECT 'e%'
UNION SELECT 'i%'
UNION SELECT 'o%'
UNION SELECT 'u%'
) f ON n.LastName LIKE f.FilterMatch

 

This makes your filter a column, and if needed you can add it to your select statement

SELECT f.FilterMatch, n.LastName
FROM NameList AS n
INNER JOIN
(
SELECT 'a%' FilterMatch
UNION SELECT 'e%'
UNION SELECT 'i%'
UNION SELECT 'o%'
UNION SELECT 'u%'
) f ON n.LastName LIKE f.FilterMatch

 

Lastly, if you need to join this to more tables, just add more joins.

SELECT f.FilterMatch, n.LastName, n.CustID, o.ordernumber, o.orderdate, o.ordertotal
FROM NameList AS n
INNER JOIN
(
SELECT 'a%' FilterMatch
UNION SELECT 'e%'
UNION SELECT 'i%'
UNION SELECT 'o%'
UNION SELECT 'u%'
) f ON n.LastName LIKE f.FilterMatch
INNER JOIN
Orders AS o ON n.CustID = o.CustID

 

Comments are closed.