How to do a negative lookahead in oracle regexp_like(), '?!' isn't working

Question

Query:

select 1 "val" from dual where regexp_like('ITEM HEIGHT','^(?!ICON).*HEIGHT$'); 

The above query doesn't return me 1. Please let me know how to achieve negative lookahead using oracle regexp_like().

Note: Please don't suggest any changes in query, I am interested to know the right regular expression that's accepted by regexp_like() for negative lookahead. Also [^] seems to negate character by character only but not the entire string.

Answer 1

As mentioned by others, zero-width assertions are not directly supported by Oracle regular expressions.

In some situations you may be able to split what you are trying to check into multiple expressions. For the example you gave you could do something like this:

select 1 "val"
 from dual
where NOT regexp_like('ITEM HEIGHT','^ICON')
  and regexp_like('ITEM HEIGHT','HEIGHT$');

If you really need to do it in a single expression you may be able to use alternation with character classes to check one letter at a time like so:

select 1 "val"
 from dual
where regexp_like('ITEM HEIGHT','^([^I]|I[^C]|IC[^O]|ICO[^N]).*HEIGHT$');

Basically the first part of this expression is checking that:

• the first character is not "I"
• OR the first character is "I", but the second character is not "C"
• OR the first two characters are "IC", but the third character is not "O"
• OR the first three characters are "ICO", but the fourth character is not "N"

Obviously this method can get cumbersome quickly, but it can still be helpful in some cases.