Debian

Thanks for the report.

Reproducible as described, and I don't see any explanation for it.
If it's expected on whatever reason, it probably needs to be documented.

This is not a bug.
The LPAD() expression is calculated before REGEX_REPLACE.
So you effectively run this query:

SELECT REGEXP_REPLACE('aaa 123 bbb 45 ccc 6 ddd', '([0-9]+)', ' 00\\1');

It always adds 2 zeros to numbers as expected.

As a possible option you can call REGEXP_REPLACE recursively like this:

SELECT REGEXP_REPLACE(

       REGEXP_REPLACE(

       REGEXP_REPLACE('aaa 123 bbb 45 ccc 6 ddd',

        '(?<=[ ])([0-9]{1})(?=[ ])', '000\\1'),

        '(?<=[ ])([0-9]{2})(?=[ ])', '00\\1'),

        '(?<=[ ])([0-9]{3})(?=[ ])', '0\\1') AS res;

+--------------------------------+

| res                            |

+--------------------------------+

| aaa 0123 bbb 0045 ccc 0006 ddd |

+--------------------------------+

Another option is to write a stored function and call REGEXP_REPLACE in a loop, replacing sequences with different lengths on separate iterations.

You may also try to ask on PCRE forums. Perhaps it can be done using a single call with some tricky regular expression syntax.

I googled for pcre pad all numbers with zeros to certain length and found this link:

https://stackoverflow.com/questions/48891333/pad-number-with-variable-length-zeroes-using-regex

People suggest two alternatives:

• Call replace recursively, once for each length.
• Use a too complex regular expression with a dictionary.