SQL regexp - replace dots between two numbers

Question

I need to change this;

J S49-1:7.5-190 d.

to this;

J S49-1:7,5-190 d.

by replacing all dots with commas, but only between numbers.

Answer 1

With capture groups within REGEXP_REPLACE;

select REGEXP_REPLACE('J S49-1:7.5-190 d.', '(.*\d)\.(\d.*)', '\1,\2') from dual;

will return;

J S49-1:7,5-190 d.

But this will not work if there are multiple occurrences of periods between digits.

In case there are more than one occurrence of period between digits

I was able to trigger multi replace with capture groups;

select REGEXP_REPLACE('J S49-1:7.557.8-190 d.', '(\d)\.(\d)', '\1,\2', 1, 0) from dual;

will return;

J S49-1:7,557,8-190 d.

The last two parameters are; start_position and nth_appearance, to replace all occurrences, nth_appearance value must be 0

But still there is a problem when two capture groups share a digit, this is due to overlapping (same issue as this), and needs lookahead to resolve, but can't use it in oracle, hmmm.

In case there are more than one occurrence of period between digits, and same digit is shared between two occurrence

One solution would be to apply the secondary REGEXP_REPLACE I've shared twice, so any overlapping cases would be covered in the second run;

select REGEXP_REPLACE(
                 REGEXP_REPLACE('J S49-1:7.5.8.7-190 d.', '(\d)\.(\d)', '\1,\2', 1, 0), 
                 '(\d)\.(\d)', '\1,\2', 1, 0) from dual;

will be the correct result;

J S49-1:7,5,8,7-190 d.

For more detail on REGEXP_REPLACE, check here