Oracle regex expression evaluates to false-CodePudding

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?

CodePudding user response：

You need to use

select * from device
where regexp_instr(x, '^[-[:alnum:]. _@]{1,120}$') > 0

Note that \w shorthand character class cannot be used inside a bracket expression. Since \w matches letters, digits and underscore, you can "split" it into [:alnum:] and _ patterns. As there is a _ already, all you need to replace the \w with is [:alnum:], the pattern that matches any alphanumeric chars.

CodePudding user response：

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.