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
2
-
you cannot have it for "any" length. the replacement will cause overflow.Kirill Leontev– Kirill Leontev2019-12-03 12:14:47 +00:00Commented Dec 3, 2019 at 12:14
-
So what if I need it for max 4 of them like this sentence?savemenico– savemenico2019-12-03 12:27:13 +00:00Commented Dec 3, 2019 at 12:27
Add a comment
|
1 Answer
I doubt that a single REGEXP_REPLACE is sufficient for your task, but you can do it in 2 steps:
- Add some 0-digits before any number so that is at least your desired length.
- 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
