Oracle SQL Regexp_replace matching

Question

Here is a funky matching that I need to accomplish.

A5.1.9.11.2

Needs to become:

A05.01.09.11.02

The amount of DOT sections will vary from none to many. And the letter "A" will always be there and always be 1 character.

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

Andrew Cheong · Accepted Answer · 2012-09-25 14:19:59Z

3

Oracle SQL doesn't support lookaround assertions, which would be useful for this case:

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

You'll have to use at least two replacements:

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

answered Sep 25, 2012 at 14:19

Andrew Cheong

30.4k17 gold badges103 silver badges173 bronze badges

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

1 Comment

+1 I got stranded on the lookaround problem. Using two REGEXP_REPLACE() calls, is not intuitive but it does work.