CODEWITHSUNDEEP
sqlregexoracle-databasereplacematching
Gabe Ortiz
Gabe Ortiz

Reputation: 147

Oracle SQL Regexp_replace matching

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.

Upvotes: 2

Views: 1205

Answers (1)

Andrew Cheong
Andrew Cheong

Reputation: 30293

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')

Upvotes: 3

Related Questions