Oracle SQL Regexp_replace Compliance

Question

Here is the fake match I need to fulfill.

A5.1.9.11.2

Need to become:

A05.01.09.11.02

The number of DOT sections will vary from one to several. And the letter "A" will always be there and there will always be 1 character.

I would like to use the regexp_replace () function to use this as a sorting mechanism. Thanks.

Answer 1

Oracle SQL does not support search statements that would be useful for this case:

 s/([0-9](?<![0-9]))/0\1/g 

You will need to use at least two replacements:

 REGEXP_REPLACE(REGEXP_REPLACE(col, '([0-9]+)', '0\1'), '0([0-9]{2})', '\1')