Can we exclude match with few strings in oracle regexp_like()?

Question

Background Knowledge:

1. We can't use (?!) to exclude, since, regexp_like() doesn't support negative lookahead.
2. I don't want to exclude using 'NOT REGEXP_LIKE()'
3. [^] can negate a single character only but not a string

Question:

Interested to know if we have any alternative to change the regular expression itself being passed to oracle regexp_like().

Example scenario to explain:

Regexp - "STANDARD.*TIME" when used in regexp_like() would match all time zones containing both words STANDARD and TIME. Say I want to exclude 'INDIAN STANDARD TIME', 'ATLANTIC STANDARD TIME', 'IRISH STANDARD TIME' from the matched time zones

Answer 1

I would be interested to know why using 'NOT' is out of the question. But if you are looking for a regex solution for the fun of it, I don't think REGEXP_LIKE is going to work as the Oracle flavor does not support negative look-aheads. However, thinking outside of the box a little, and knowing that REGEX_REPLACE returns NULL if the pattern is not found, you could do something like this (although just because you can does not mean you should and I would use NOT with REGEXP_LIKE):

SQL> with tbl(str) as (
     select 'INDIAN STANDARD TIME' from dual union all
     select 'ATLANTIC STANDARD TIME' from dual union all
     select 'IRISH STANDARD TIME' from dual union all
     select 'EASTERN STANDARD TIME' from dual union all
     select 'PST STANDARD TIME' from dual union all
     select 'CST STANDARD TIME' from dual
   )
   select str
   from tbl
   where str = regexp_replace(str, '^(INDIAN|ATLANTIC|IRISH) STANDARD.*TIME', 'DO NOT WANT THESE');

STR
----------------------
EASTERN STANDARD TIME
PST STANDARD TIME
CST STANDARD TIME

SQL>

So this replaces the strings you don't want then compares them. Since the match is not found the select does not return them. Still not as clean as:

select str
from tbl
where NOT regexp_like(str, '^(INDIAN|ATLANTIC|IRISH) STANDARD.*TIME');