Oracle: Mixing IN and LIKE, is it possible?

Question

Is it possible to do someting like this?

Ie.

IN ('%1', '%2')

The values are not few

How would you solve this task?

Answer 1

You could also use REGEXP_LIKE instead of LIKE. It can be more flexible in this type of situation.

To get everything that ends with '1' or '2' just use:

SELECT * FROM table_name WHERE REGEXP_LIKE(col_name, '[12]$')

The [12] means match 1 or 2. The $ means match the end of the string.

If you need something such as this:

LIKE '%1' OR LIKE '%2' or LIKE '%A' or LIKE '%W'

... you just have to add the other characters within the square brackets:

SELECT * FROM table_name WHERE REGEXP_LIKE(col_name, '[12AW]$')

Or, if you wanted anything that ends with a digit you could use this:

SELECT * FROM table_name WHERE REGEXP_LIKE(col_name, '[[:digit:]]$'

Answer 2

If you have only a few, just combine them with or:

column like '%1' or column like '%2' or...

If you have a lot, you can create a table with the patterns and join to it.

create table matches (
   match char(10)
);

insert into matches values
    ('%1'),('%2'),...;

select * from table
    inner join matches on table.column like matches.match;

That has some issues with duplicate rows if a single row matches more than one pattern, but you can modify it depending on what kind of final output you want.

As a third option, you could use substr() rather than like if they are all the same length:

select * from table where substr(column,-1,1) in ('1','2',...)

This checks if the last character is in the set of values.

Answer 3

How about using like itself?

Column LIKE '%1' OR Column LIKE '%2'

Answer 4

Basically same as examples of others but closest to your example:

SELECT deptno, empno, ename FROM scott.emp
 WHERE job IN 
   (SELECT job FROM scott.emp WHERE job Like ('SALE%') OR job Like ('MANAG%'))
/