Oracle REGEX_REPLACE function and capture groups

Question

I have a string that i would like to convert from a sequence of strings to a sequence of strings separated by a hyphen. Example

200400116828 --> 2004-001168-28

For String to be converted, the input string must follow these rules:

- Starts with a 1 or 2
- Followed by three digits
- Followed by 6 digits
- Followed by 2 digits

I use a Regex to extract the above groups from the input string to build the output string using the Regex '^([12]\d{3})(\d{6})(\d{2})$'

I managed to get it to work using the following query:

Select REGEXP_REPLACE(
            '200400116828','^([12]\d{3})(\d{6})(\d{2})$','\1-\2-\3'
          ) from dual;

Output - 2004-001168-28

But i am confused by the following query also works but with a wrong output:

Select REGEXP_REPLACE(
            '200400116828','^([12]\d{3})(\d{6})(\d{2})$','\10-\11-\12'
          ) from dual;

Output - 20040-20041-20042

Could somebody please explain the output of the 2nd query because to me it does not match the RegEx provide.

During Replace, \10 is treated as \1 followed by a 0

Matt.G
– Matt.G

2019-03-01 14:01:10 +00:00
Commented Mar 1, 2019 at 14:01 — Matt.G
– Matt.G, Commented Mar 1, 2019 at 14:01

Wiktor Stribiżew · Accepted Answer · 2019-03-01 14:03:55Z

2

The regex is fine, you are confused with the replacement pattern.

Oracle regex engine is POSIX based and replacement patterns only support backreferences from 1 through 9. What can't be parsed as a backreference is parsed as literal text.

Hence, \10-\11-\12 is parsed as Group 1 value, 0-, Group 1 value, 1-, Group 1 value, 2.

Also, see regexp_replace documentation:

The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9.

answered Mar 1, 2019 at 14:03

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

3 Comments

ziggy Over a year ago

Ah that explains it. If i have a regex with more than 9 capture groups, how would i access the 10th, 11th etc groups?

Wiktor Stribiżew Over a year ago

@ziggy There is no way to reference the tenth backreference and further in Oracle regex. This limitation is documented.

ziggy Over a year ago

Thanks for your quick response.

Collectives™ on Stack Overflow

Oracle REGEX_REPLACE function and capture groups

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related