CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Robert Wells
Robert Wells

Reputation: 33

Regexp_replace adding 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

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.

Upvotes: 3

Views: 150

Answers (1)

Mat
Mat

Reputation: 206699

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

Upvotes: 4

Related Questions