How can I introduce multiple conditions in LIKE operator?

I want to write an SQL statement like below:

select * from tbl where col like ('ABC%','XYZ%','PQR%');

I know it can be done using OR. But I want to know is there any better solution.

Solution 1:

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);

Solution 2:

Oracle 10g has functions that allow the use of POSIX-compliant regular expressions in SQL:

• REGEXP_LIKE
• REGEXP_REPLACE
• REGEXP_INSTR
• REGEXP_SUBSTR

See the Oracle Database SQL Reference for syntax details on this functions.

Take a look at Regular expressions in Perl with examples.

Code :

    select * from tbl where regexp_like(col, '^(ABC|XYZ|PQR)');

Solution 3:

Here is an alternative way:

select * from tbl where col like 'ABC%'
union
select * from tbl where col like 'XYZ%'
union
select * from tbl where col like 'PQR%';

Here is the test code to verify:

create table tbl (col varchar(255));
insert into tbl (col) values ('ABCDEFG'), ('HIJKLMNO'), ('PQRSTUVW'), ('XYZ');
select * from tbl where col like 'ABC%'
union
select * from tbl where col like 'XYZ%'
union
select * from tbl where col like 'PQR%';
+----------+
| col      |
+----------+
| ABCDEFG  |
| XYZ      |
| PQRSTUVW |
+----------+
3 rows in set (0.00 sec)