How can I introduce multiple conditions in LIKE operator?

by

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

More Related Contents:

  1. How do I limit the number of rows returned by an Oracle query after ordering?
  2. How to create id with AUTO_INCREMENT on Oracle?
  3. When do I need to use a semicolon vs a slash in Oracle SQL?
  4. LISTAGG in Oracle to return distinct values
  5. Oracle SQL: Update a table with data from another table
  6. Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]
  7. How to handle a single quote in Oracle SQL
  8. Select count(*) from multiple tables
  9. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  10. Using ‘case expression column’ in where clause
  11. Oracle SQL escape character (for a ‘&’)
  12. SQL Error: ORA-00933: SQL command not properly ended
  13. Convert a string date into datetime in Oracle
  14. WHERE IN condition not accepting String value
  15. What is the difference between Views and Materialized Views in Oracle?
  16. Benefits Of Using SQL Ordinal Position Notation?
  17. ORA-00907: missing right parenthesis
  18. Unordered results in SQL
  19. Oracle Dynamic Pivoting
  20. What is it exactly a BLOB in a DBMS context
  21. Custom Order in Oracle SQL
  22. How do I list all tables in a schema in Oracle SQL?
  23. Is there a version control system for database structure changes?
  24. oracle sql: update if exists else insert [duplicate]
  25. Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
  26. Compare Strings ignoring accents in SQL (ORACLE)
  27. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  28. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  29. Exporting a CLOB to a text file using Oracle SQL Developer
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment