Oracle sql REGEXP_REPLACE expression to replace a number in a string matching a pattern

Question

I have a string 'ABC.1.2.3' I wish to replace the middle number with 1.

Input 'ABC.1.2.3'
Output 'ABC.1.1.3'

Input 'XYZ.2.2.1'
Output 'XYZ.2.1.1'

The is, replace the number after second occurrence of '.' with 1.

I know my pattern is wrong, the sql that I have at the moment is :

select REGEXP_REPLACE ('ABC.1.2.8', '(\.)', '.1.') from dual;

Answer 1

You can use capturing groups to refer to surrounding numbers in replacement string later:

select REGEXP_REPLACE ('ABC.1.2.8', '([0-9])\.[0-9]+\.([0-9])', '\1.1.\2') from dual;