CODEWITHSUNDEEP
oracle-databaseregexp-replace
savemenico
savemenico

Reputation: 45

regexp replace format all numbers the same in a string (Oracle)

Lets say I have

"Hello this is 11 and i want 0032 but there is 013 and 5"

"Hello this is 0011 and i want 0032 but there is 0013 and 0005"

EDITED

Upvotes: 0

Views: 82

Answers (1)

Radagast81
Radagast81

Reputation: 3006

I doubt that a single REGEXP_REPLACE is sufficient for your task, but you can do it in 2 steps:

  1. Add some 0-digits before any number so that is at least your desired length.
  2. Delete leading 0-digits until you get your desired length

As code it looks like this:

SELECT 
  REGEXP_REPLACE(
    REGEXP_REPLACE('Hello this is 11 and i want 0032 but there is 013 and 5'
                  ,'(\d+)','000\1') -- Add three 0-digits to any number
                ,'0+(\d{4})','\1') -- Remove all 0-digits prior to the last 4 digits of any number
  FROM dual

Result:

Hello this is 0011 and i want 0032 but there is 0013 and 0005

Upvotes: 1

Related Questions