By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH t(table_name) AS
(
SELECT 'TEMPLE' FROM dual UNION ALL
SELECT 'TEMPORARY' FROM dual UNION ALL
SELECT 'TEMPLATED' FROM dual UNION ALL
SELECT 'TEMPLATE' FROM dual UNION ALL
SELECT 'EMPLATE' FROM dual
)
SELECT *
FROM t
WHERE ( table_name NOT LIKE '%TEMP%' AND table_name NOT LIKE '%TEMPLATE%' )
OR table_name LIKE '%TEMPLATE%'
| TABLE_NAME |
|---|
| TEMPLATED |
| TEMPLATE |
| EMPLATE |