Exception for 'NOT LIKE' in Oracle

in a database are many tables with the name 'D_...'. In a search query, all tables with 'D_%' should be ignored, except one. How do I handle this exception?

CodePudding user response：

If I understood you well, you would need something like this sql. Adding escape option to not like clause will make oracle consider "_" as a simple character.

select * 
from user_tables
where table_name not like 'D/_%' escape '/'  
  or table_name in ('YOUR_EXCEPTION_TABLE_NAME')
;

CodePudding user response：

You can use NOT LIKE and escape the underscore:

SELECT *
FROM   your_table
WHERE  your_column NOT LIKE 'D\_%' ESCAPE '\';

Which, for the sample data:

CREATE TABLE your_table (your_column) AS
SELECT 'ABC'   FROM DUAL UNION ALL
SELECT 'DEF'   FROM DUAL UNION ALL
SELECT 'D_XYZ' FROM DUAL;

Outputs:

YOUR_COLUMN

ABC

DEF

YOUR_COLUMN
ABC
DEF

db<>fiddle here