0

The following Oracle regular expressions do not work and I don't know why.

"Does not start with 'abc'":

^[^(abc)]

"Does not end with 'abc'":

[^(abc)]$

The problem is that the Oracle regex engine does not seem to recognize the 'abc' string as a unit, but only is looking at the letters individually. The parentheses () are supposed to create a string unit. So I don't know what is going on. I used the square brackets only because I believe the 'not' operator ^ only operates inside the brackets, otherwise the ^ is recognized as start of line.

For reference: http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm

Alexander Mills
  • 90,741
  • 139
  • 482
  • 817

2 Answers2

4

Testing for non-matching like this can get complicated, so I'd recommend testing for a match and negating the result.

Does not start with abc:

WHERE NOT REGEXP_LIKE(myString, '^abc')

Does not end with abc:

WHERE NOT REGEXP_LIKE(myString, 'abc$')

As for why it doesn't work, as @DavidKnipe says in his answer: it's because you're using character classes. The regex ^[^(abc)] parses out like this:

  • The first ^ says "anchor to the beginning of the string"
  • The [^(abc)] is a character class that says "match any single character as long as it's not ( or a or b or c or )".
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
0

The ^ operator for negation is only for character classes. But you shouldn't be using character classes. I don't know whether Oracle regexes allow lookaround operations (lookahead and lookbehind). If they do, use ^(?!abc) and (?<!abc)$.

David Knipe
  • 3,417
  • 1
  • 19
  • 19