Using Regular expression to replace where clause in select statement

Question

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

Answer 1

You might have included an extra WHERE

SELECT table_name
FROM all_tables
WHERE owner = 'XXXXXXXX' AND
REGEXP_LIKE(table_name,'(.*)((A|B|C)$)', 'i') AND
NOT REGEXP_LIKE(table_name,'^(P|Q)(R|S)D(.*)(_D$)', 'i');