Regexp_replace add extra characters
I am using the following query in Oracle 11.2.0.3.0 / Toad for Oracle 11.6.1.6:
select regexp_replace('000010PARA197427'
,'([0-9]*)([A-Z]*)([0-9]*)'
,'\3-\2-\1') from dual
Instead of getting what I expected 197427-PARA-000010
. As a result, I get 197427-PARA-000010--
.
If I change the query to:
select regexp_replace('000010PARA197427'
,'([0-9]*)([A-Z]*)([0-9]*)'
,'\3-c\2-c\1') from dual
Then I get 197427-cPARA-c000010-c-c
for the result.
it, like all literals, is appended to the end of the result.
Any help would be much appreciated.
source to share
It's not entirely clear why this is happening, but since you only have quantifiers *
and no binding, you might be getting an empty match (or something like that).
Template binding ( /^...$/
) seems to be working . Using +
rather than *
any of the quantifiers also works for this pattern.
SQL> select regexp_replace('000010PARA197427'
,'([0-9]+)([A-Z]*)([0-9]*)'
,'\3-\2-\1') foo from dual ;
FOO
------------------
197427-PARA-000010
SQL> select regexp_replace('000010PARA197427'
,'^([0-9]*)([A-Z]*)([0-9]*)$'
,'\3-\2-\1') foo from dual ;
FOO
------------------
197427-PARA-000010
source to share