Regexp_replace adding extra characters

Question

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

Rather than getting what I expected, 197427-PARA-000010. I get 197427-PARA-000010-- as a result.

If I change the query to:

select  regexp_replace('000010PARA197427'
                      ,'([0-9]*)([A-Z]*)([0-9]*)'
                      ,'\3-c\2-c\1') from dual

I then get 197427-cPARA-c000010-c-c for the result.

it's like all the literals are getting appended to the end of the result.

Any help would be much appreciated.

Mat · Accepted Answer · 2014-11-10 17:10:11Z

4

Not exactly sure why this is happening, but since you only have * quantifiers and no anchoring, maybe you're getting an empty match (or something like that).

Anchoring the pattern (/^...$/) seems to work. Using + rather than * for any of the quantifier also works for this sample.

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

answered Nov 10, 2014 at 17:10

Mat

208k41 gold badges407 silver badges423 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Regexp_replace adding extra characters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related