I have a regular expression which in my java application is used to evaluate a username ^[-\w.+_@]{1,120}$
https://regexr.com/6vhsf In Regexr, test 1,2,3 passes as expected.
I want to apply the same username check in a piece of PL/SQL code, however in Oracle the same expression always evaluates to false http://sqlfiddle.com/#!4/2c33a/156
Why does Oracle evaluate the expression differently?
Why does Oracle evaluate the expression differently?
Oracle does not match \w as a word-character within a character group it matches it as the individual characters \ and w.
Note: do not use regexr.com to test Oracle regular expressions as Oracle does not support the same regular expression syntax as is tested on that website.
If you want to match the equivalent of \w within a bracket expression then you can match the underscore _ and either 0-9a-zA-Z or the POSIX character class [:alnum:].
So either:
SELECT *
FROM device
WHERE REGEXP_LIKE(x, '^[0-9a-zA-Z.+_@-]{1,120}$')
or:
SELECT *
FROM device
WHERE REGEXP_LIKE(x, '^[[:alnum:].+_@-]{1,120}$')
Note 2: When matching - then it either needs to be first or last in the bracket expression. However, if you need to match both ] and - in a bracket expression then ] must be the first character and - the last character so it is a good habit to always put the - as the last character in the bracket.
