LIKE and IN can I combine

[rcamarda]

I can use the IN with the WHERE clause as example:

SELECT * FROM NORTHWIND WHERE LASTNAME IN ('FULLER','KING')

I want to use the IN and LIKE at the same time:
SELECT * FROM NORTHWIND WHERE LASTNAME LIKE ('A%','B%')

I know this is a simplistic example, but the members for the IN will be
many, 5 to 10.
I'm trying to avoid:
SELECT * FROM NORTHWIND WHERE LASTNAME LIKE 'A%' OR LASTNAME LIKE 'B%'
OR LASTNAME LIKE 'FU%' OR LASTNAME LIKE 'JON%' <...>

and so forth.
Any Ideas?
TIA
Rob

[Razvan Socol]

Hello, Rob

Instead of this:

SELECT * FROM employees
WHERE LastName LIKE 'C%'
OR LastName LIKE 'D%'
OR LastName LIKE 'F%'

you can use:

SELECT e.* FROM employees e INNER JOIN (
SELECT 'C' AS Prefix
UNION ALL SELECT 'D'
UNION ALL SELECT 'F'
) x ON e.LastName LIKE x.Prefix+'%'

or:

SELECT * FROM employees e
WHERE EXISTS (
SELECT * FROM (
SELECT 'C' AS Prefix
UNION ALL SELECT 'D'
UNION ALL SELECT 'F'
) x WHERE e.LastName LIKE x.Prefix+'%'
)

The last query is more correct than the previous (in case that two
prefixes could match the same name).

Of course, it's better to use a temporary table to store all the
requested prefixes.

Razvan

[Ross Presser]

On 10 Oct 2005 10:19:34 -0700, Razvan Socol wrote:

SELECT * FROM employees e
WHERE EXISTS (
SELECT * FROM (
SELECT 'C' AS Prefix
UNION ALL SELECT 'D'
UNION ALL SELECT 'F'
) x WHERE e.LastName LIKE x.Prefix+'%'
)

Is there any good reason why you wouldn't do this variation:

SELECT * FROM employees e
WHERE EXISTS (
SELECT * FROM (
SELECT 'C%' AS Pattern
UNION ALL SELECT 'D%'
UNION ALL SELECT 'F%'
) x WHERE e.LastName LIKE x.Pattern
)

[Razvan Socol]

That would be OK, too.

Razvan