I've just discovered a strange inconsistency in regexp_replace; I'm not sure if this is a bug or a feature I don't understand.
select
regexp_replace('x1234567y8z90', '^\D*\d*\D(\d{1,2}).*$', '\1') "\D"
, regexp_replace('x1234567y8z90', '^[^\d]*\d*\D(\d{1,2}).*$', '\1') "^\d"
from dual
;produces following
\D ^\d
8 90
I expected both be '8', because the only difference between the two expressions is the first one is using \D and the 2nd -- [^\d], which, in my mind, should be the same.
This is because Oracle Database treats the characters within the square brackets as literals. So:
[^\d]
means:
Match any character which isn't:
backslash
lowercase d
To get around this, wrap \d in square brackets. Or use one of the other expressions for matching numbers:
select
regexp_replace('x1234567y8z90', '^\D*\d*\D(\d{1,2}).*$', '\1') "\D"
, regexp_replace('x1234567y8z90', '^[^[\d]]*\d*\D(\d{1,2}).*$', '\1') "^\d"
, regexp_replace('x1234567y8z90', '^[^[:digit:]]*\d*\D(\d{1,2}).*$', '\1') ":digit:"
, regexp_replace('x1234567y8z90', '^[^0-9]*\d*\D(\d{1,2}).*$', '\1') "^0-9"
from dual;
\D ^\d :digit: ^0-9
8 8 8 8