How to exclude substring "TEMP" but include string "TEMPLATE" in regular expression in plsql

Question

How to exclude substring "TEMP" but include string "TEMPLATE" in regular expression in plsql.

I am trying to use condition like below but all are getting excluded

code:

SELECT TABLE_NAME
from all_tables
where not regexp_like( table_name, '_(TEMP)')
  and     regexp_like( table_name, 'TEMPLATE');

Answer 1

Do you really need regular expressions? How about

select table_name
  from all_tables
  where instr(table_name, 'TEMPLATE') > 0

If it isn't that simple (according to your comment), then consider something like this:

SQL> WITH
  2     all_tables (table_name)
  3     AS
  4        (SELECT 'MY_TEMPLATE' FROM DUAL   -- OK
  5         UNION ALL
  6         SELECT 'EXC_ABC' FROM DUAL       -- not OK, contains _ABC
  7         UNION ALL
  8         SELECT 'EMP' FROM DUAL           -- OK
  9         UNION ALL
 10         SELECT 'DEPT' FROM DUAL          -- OK
 11         UNION ALL
 12         SELECT 'YOUR_TEMPXX2' FROM DUAL) -- not OK, contains _TEMP
 13  SELECT *
 14    FROM all_tables
 15   WHERE    NOT REGEXP_LIKE (table_name, '_(TEMP|ABC|DEF|XYZ)|[[:digit]]')
 16         OR REGEXP_LIKE (table_name, 'TEMPLATE');

TABLE_NAME
------------
MY_TEMPLATE
EMP
DEPT

SQL>

Answer 2

You can use combined logic with AND with OR containing exception for the word TEMPLATE such as

 WHERE ( table_name NOT LIKE '%TEMP%' AND table_name NOT LIKE '%TEMPLATE%' )
    OR   table_name LIKE '%TEMPLATE%' 

Demo