Oracle Regexp fails in SQL

Question

I am trying to use this regexp statement:

select 1 from dual where regexp_like('040', '^[\d\*]{3}$');

No output is returned but interesting to say:

select 1 from dual where regexp_like('040', '^[[:digit:]\*]{3}$');

works.

Why?

I am on Oracle 11.2.

Answer 1

This is because Oracle only supports the POSIX regular expressions standard, rather than the Perl syntax that you use in your first example.

Oracle Docs: http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm#CHDJGBGG POSIX Regex Standard: http://pubs.opengroup.org/onlinepubs/007908799/xbd/re.html

Edit: As Alex Poole points out Oracle does actually support Perl regex syntax since Oracle 10gR2. Trying your example on my local 11gR2 install suggests that your syntax is wrong, the following works fine:

SELECT 1 FROM dual WHERE regexp_like('040', '^\d{3}$');

Answer 2

In your second example you're using a POSIX character class. In your first example you're mixing POSIX and 'Perl-influenced extensions'. The matching character-list enclosed in [ ... ]:

Matches any single character in the list within the brackets. The following operators are allowed within the list, but other metacharacters included are treated as literals ...

So within the [], the backslash metacharacter is treated as a literal backslash, which you're looking for any of the characters \, d or *, and not for a single digit as you expect - the \d just isn't interpreted like that within the brackets. And you don't have any of those literal characters in your sample string, therefore it doesn't find a match.

Even in your second version, the \* is only going to match those literal characters too, so that isn't adding anything; unless you want to match a value like '12*' or '1\2', which seems unlikely.

So you may just want the simpler:

select 1 from dual where regexp_like('040', '^\d{3}$');

or the equivalent:

select 1 from dual where regexp_like('040', '^[[:digit:]]{3}$');