How to combine IN operator with LIKE condition (or best way to get comparable results)

Question

I need to select rows where a field begins with one of several different prefixes:

select * from table 
where field like 'ab%' 
  or field like 'cd%' 
  or field like "ef%" 
  or...

What is the best way to do this using SQL in Oracle or SQL Server? I'm looking for something like the following statements (which are incorrect):

select * from table where field like in ('ab%', 'cd%', 'ef%', ...)

or

select * from table where field like in (select foo from bar)

EDIT: I would like to see how this is done with either giving all the prefixes in one SELECT statement, of having all the prefixes stored in a helper table.

Length of the prefixes is not fixed.

Answer 1

Joining your prefix table with your actual table would work in both SQL Server & Oracle.

DECLARE @Table TABLE (field VARCHAR(32))
DECLARE @Prefixes TABLE (prefix VARCHAR(32))

INSERT INTO @Table VALUES ('ABC')
INSERT INTO @Table VALUES ('DEF')
INSERT INTO @Table VALUES ('ABDEF')
INSERT INTO @Table VALUES ('DEFAB')
INSERT INTO @Table VALUES ('EFABD')

INSERT INTO @Prefixes VALUES ('AB%')
INSERT INTO @Prefixes VALUES ('DE%')

SELECT  t.*
FROM    @Table t
        INNER JOIN @Prefixes pf ON t.field LIKE pf.prefix 

Answer 2

you can try regular expression

SELECT * from table where REGEXP_LIKE ( field, '^(ab|cd|ef)' );