How can i introduce multiple conditions in LIKE operator

sql oracle
Madhu picture Madhu · Sep 7, 2009 · Viewed 389.1k times · Source

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.

Answer

Bill Karwin picture Bill Karwin · Sep 7, 2009

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

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More