0

Do you know why this SQL command on Oracle 11g R2 Express Edition (XE) does not return expected result ?

SELECT 'X'
FROM dual
WHERE REGEXP_LIKE('Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)', '^.*MSIE [5-8](?:\.[0-9]+)?(?!.*Trident\/(?:[5-9]|1[0-9])\.0).*$');

When I use site https://regex101.com/, the pattern matches ...

Siyual
  • 16,415
  • 8
  • 44
  • 58
Vince
  • 9
  • you should use `regexp_substr` to get the actual match. using `regexp_like` will tell you if the pattern was a match or not. In this case you would get `x` if true and no row otherwise. – Vamsi Prabhala Apr 29 '16 at 13:58
  • Oracle regex engine does not support lookaheads. If you were trying to get substring, I would suggest using `regexp_substr` and then replacing unnecessary part using `regexp_replace` instead. You probably don't need them at all if you just need to match line with `regexp_like`. – Paul Apr 29 '16 at 14:02
  • Thanks for your answer but this command returns nothing. So, for me, there is something that Oracle does not recognize in the regular expression (second argument). But what ? select REGEXP_SUBSTR('Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)', '^.*MSIE [5-8](?:\.[0-9]+)?(?!.*Trident\/(?:[5-9]|1[0-9])\.0).*$') "REGEXP_SUBSTR" from dual; – Vince Apr 29 '16 at 14:06

1 Answers1

0

I´m wondering, how regexe101 can match, because you look for "...Trident/4.0 ..." with a pattern, that expects only digits between 5 and 9 after the slash (which definitely does not contain a 4, so regex101 shouldn´t match!).

Anyway, after correcting this and removing those ?: and ?! modifiers, which are obviously not handled correct by 11gR2 (and even 12c), it works:

SELECT 'X'
FROM dual
WHERE REGEXP_LIKE(
    'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)'
  , '^.*MSIE [5-8](?:\.[0-9]+)?(.*Trident\/([4-9]|1[0-9])\.0).*$');
oratom
  • 271
  • 1
  • 5
  • Thanks for your answer. With the ?!, it was supposed to be considered as a negation. So, avoid selection of Trident/ with a digit of 5 to 9 + .0 (Example: Trident/5.0) OR with digits 1x (where x = 0 to 9) (Example: Trident /11.0) – Vince Apr 29 '16 at 14:11
  • Ah, i see ... but afaik, Oracle Regex don´t know about negative lookahead / lookbehind, so you have to modify your pattern to something like `'^.*MSIE [5-8](\.[0-9]+)?(.*Trident\/([^5-9]|1[0-9])\.0).*$ ` – oratom Apr 29 '16 at 15:06