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;

revo · Accepted Answer · 2018-03-13 10:46:46Z

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;

answered Mar 13, 2018 at 10:46

revo

49.1k15 gold badges84 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vick_4444 Over a year ago

Thanks, this worked, can you please explain what the pattern and replace strings are, I understand that pattern is "number.number.number", but not able to figure what the replace string mean

revo Over a year ago

first and third number are inside a capturing group. Capturing groups let us to refer to their stored value later on replacement string. \1 is a back-reference to capturing group one and \2 is a back-reference to capturing group two. We remove whole thing then rebuild with ours. @vick_4444

vick_4444 Over a year ago

Understood. Thank you very much.

Jan · Accepted Answer · 2018-03-13 10:41:57Z

1

You could use

^([^.]*\.[^.]*\.)\d+(.*)

See a demo on regex101.com.

This is:

^                # start of the string
([^.]*\.[^.]*\.) # capture anything including the second dot
\d+              # 1+ digits
(.*)             # the rest of the string up to the end

This is replaced by

$11$2

answered Mar 13, 2018 at 10:41

Jan

43.3k11 gold badges57 silver badges87 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related