Combining LIKE with IN in SQL

Question

Instead of executing:

SELECT a
FROM b
WHERE a LIKE 'a%'
OR a LIKE 'b%'
OR a LIKE 'c%'


is there a way to execute something functioning like this pseudocode?

SELECT a
FROM b
WHERE a IN ('a%', 'b%', 'c%')

Answer 1

Might be too specific to your example, but you could do LIKE '[a-c]%'. Other than that, I'm not aware of any LIKE-like IN syntax

Answer 2

No. The IN operator expects actual values. The closest you could get is something like:

Select a
from b 
where a in (
    select a 
    from b
    WHERE a LIKE 'a%'
    OR a LIKE 'b%'
    OR a LIKE 'c%')

Except that just duplicates your efforts for this particular case, so I wouldn't use the above code. Essentially, you need to pass in the actual values into your IN statement. The takeaway here is that you can use a select statement inside an IN statement to get your values.

Also, I am operating on the assumption here that you are not trying to select literal values 'a', 'b', 'c', etc., and that these value represent some other string that you don't care to show us.

Answer 3

You could use

SELECT a
FROM b
WHERE a >= 'A'
AND a < 'D'

which would assure you the best use of an index on a.

I bet the other answer will give you an index scan or table scan.

Answer 4

You could dump your criteria values into a temp table (without the wildcard). Then do a join like:

SELECT b.a FROM b INNER JOIN #likevals on b.a LIKE #likevals.value + '%'

WHERE #likevals looks like:

value
------
a
b
c