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

you can try regular expression

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

Answer 2

If those prefixes going to change, then i would put them in a table.

Answer 3

If your prefix is always two characters, could you not just use the SUBSTRING() function to get the first two characters of "field", and then see if it's in the list of prefixes?

select * from table
where SUBSTRING(field, 1, 2) IN (prefix1, prefix2, prefix3...)

That would be "best" in terms of simplicity, if not performance. Performance-wise, you could create an indexed virtual column that generates your prefix from "field", and then use the virtual column in your predicate.

Answer 4

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 5

Depending on the size of the dataset, the REGEXP solution may or may not be the right answer. If you're trying to get a small slice of a big dataset,

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

may be rewritten behind the scenes as

select * from table   
 where field like 'ab%'   
union all
select * from table
 where field like 'cd%'   
union all
select * from table
 where field like 'ef%' 

Doing three index scans instead of a full scan.

If you know you're only going after the first two characters, creating a function-based index could be a good solution as well. If you really really need to optimize this, use a global temporary table to store the values of interest, and perform a semi-join between them:

select * from data_table
 where transform(field) in (select pre_transformed_field
                              from my_where_clause_table);