CODEWITHSUNDEEP
sqloracle-databaseregexp-like
Vince
Vince

Reputation: 9

Oracle SQL REGEXP_LIKE not returning expected result

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 ...

Upvotes: 0

Views: 248

Answers (1)

oratom
oratom

Reputation: 291

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).*$');

Upvotes: 0

Related Questions