How to use substr inside regexp_replace?
"Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0"
I want to truncate numbers with many decimals inside a string using REGEXP_REPLACE.
Original string: '10.030378963850, 54.381100865715318093'
Target string: '10.03037, 54.38110'
I tried:
select REGEXP_REPLACE('10.030378963850, 54.381100865715318093', '([-+]?[0-9]*.?[0-9]+)', SUBSTR('\1', 0, 8)) from dual;
Result: 10.030378963850, 54.381100865715318093
- seems that SUBSTR was completelly ignored.
select REGEXP_REPLACE('10.030378963850, 54.381100865715318093', '([-+]?[0-9].?[0-9]+)', 'aa') from dual;
Result: aa, aa
- regexp identified correctly the number and replaced them with a fixed string.
select REGEXP_REPLACE('10.030378963850, 54.381100865715318093', '([-+]?[0-9].?[0-9]+)', 'aa'||'\1') from dual;
Result: aa10.030378963850, aa54.381100865715318093
0