Basically I would like to replace the where clause from the below select statement using regex. (here there are three condition; 1. Owner name 2. table to be included 3. table to be excluded)
SELECT * FROM all_tables WHERE owner = 'XXXXXXXX' AND (table_name LIKE '%_\_A' ESCAPE '\' OR table_name LIKE '%_\_B' ESCAPE '\' OR table_name LIKE '%_\_C' ESCAPE '\') AND (table_name NOT LIKE statement for (P|Q)(R|S)D(.*)(_D$)')
What I have done so far?
In below select statement I wrote REGEXP_LIKE to get table name ending with A or B or C and same NOT REGEXP_LIKE for those table names which follow a pattern like (P|Q)(R|S)*(_D)) and needs to be excluded.
I know this way REGEXP_LIKE and NOT REGEXP_LIKE wont work, Can anyone please help me to find one single regex for the above 2 (regexp_like and not regexp_like). Thank you very much
SELECT table_name FROM all_tables WHERE owner = 'XXXXXXXX' AND REGEXP_LIKE (table_name, '(.*)((A|B|C)$)', 'i') AND WHERE NOT REGEXP_LIKE(table_name,'^(P|Q)(R|S)D(.*)(_D$)', 'i');