3

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.

user2907301
  • 71
  • 1
  • 6
  • 1
    Not sure if it's what you want, but if you want to negate values, try this: 'a[^b]' – Renato Afonso Sep 26 '17 at 13:41
  • 6
    Oracle Regexp's do not support lookahead. – Patrick Bacon Sep 26 '17 at 13:44
  • My doubt is what if I have to exclude a string instead of a character? – user2907301 Sep 28 '17 at 06:00
  • Not sure if in the meantime you learned more about regular expressions... In your attempt, the caret is actually an anchor at the beginning of the string. Since the unescaped parenthesis is a metacharacter, the question mark after it is actually a syntax error. Big Brother Oracle chooses to ignore that error - it simply ignores the question mark. Then it continues to match: it looks for strings that begin with `'!ICON'` and contain one more character (other than newline) and `'HEIGHT'`. **Nothing at all** about **excluding** anything! –  Apr 14 '20 at 00:45

2 Answers2

8

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.

acey_zero
  • 81
  • 2
  • 3
2

Oracle does not support look-ahead in regular expressions - instead you can just check the next character is not the one you want to exclude:

select 1 "val"
from   dual
where  regexp_like('ac','a([^b]|$)');
MT0
  • 143,790
  • 11
  • 59
  • 117
  • [^] is only used to negate a single character. What if I want to negate a string? select 1 "val" from dual where regexp_like('ICON HEIGHT','^([^ICON].*HEIGHT)'); doesn't serve my purpose of negating 'ICON HEIGHT' since it goes character by character not as a single string. What's the solution for this? – user2907301 Sep 27 '17 at 05:00
  • select 1 "val" from dual where regexp_like('IA HEIGHT','^([^ICON].*HEIGHT)'); should return me 1 but it doesn't since ^ only negates a single character. – user2907301 Sep 27 '17 at 05:03