In oracle how to combine use in ('') and like-CodePudding

Is there a way to combine IN and LIKE operators together in oracle? For example I would like to search a bunch of string in ('12','123') ,but i don't exactly know inside '' ,so i want use in and like operation in this statement.

CodePudding user response：

This is a good use of a temporary table.

CREATE TEMPORARY TABLE patterns (
  pattern VARCHAR(20)
);

INSERT INTO patterns VALUES ('ABC%'), ('XYZ%'), ('PQR%');

SELECT t.* FROM tbl t JOIN patterns p ON (t.col LIKE p.pattern);

In the example patterns, there's no way col could match more than one pattern, so you can be sure you'll see each row of tbl at most once in the result. But if your patterns are such that col could match more than one, you should use the DISTINCT query modifier.

SELECT DISTINCT t.* FROM tbl t JOIN patterns p ON (t.col LIKE p.pattern);

CodePudding user response：

You can use REGEXP_LIKE() function in order to combine those operators such as

SELECT *
  FROM t 
 WHERE REGEXP_LIKE(col,'12')

Demo