Oracle regular expression REGEXP_REPLACE

Question

I would like to replace a particular string format (ignoring the numbers appearing after ^).

Ex: Here, i would like to replace all occurrences of string [XYZ^abc^0^0] where any digits can come up in place of ^0^0, like [XYZ^abc^0^0] or [XYZ^abc^20^10], etc..

Input string: [XYZ^abc^0^1][dfgf^fgfgf^0^0][ggfgf^ererer^0^0][XYZ^abc^20^1][mkkfg^oorjj^0^0][XYZ^abc^0^0]

Expected output: [dfgf^fgfgf^0^0][ggfgf^ererer^0^0][mkkfg^oorjj^0^0]

I tried many combinations including the below without any success:

SELECT
  REGEXP_REPLACE('[XYZ^abc^0^1][dfgf^fgfgf^0^0][ggfgf^ererer^0^0][XYZ^abc^20^1][mkkfg^oorjj^0^0][XYZ^abc^0^0]',
                 '[XYZ^abc^\^[[:digit:]]{1,}\^[[:digit:]]{1,}\]'
                 ) "REGEXP_REPLACE" from dual

Appreciate your help!

Thanks!

Replace \[XYZ\^abc\^\d+\^\d+\] with nothing.

user557597
– user557597

2018-08-17 22:31:57 +00:00
Commented Aug 17, 2018 at 22:31 — user557597
– user557597, Commented Aug 17, 2018 at 22:31
Awesome... thanks so much! It works..!

rash
– rash

2018-08-18 19:42:56 +00:00
Commented Aug 18, 2018 at 19:42 — rash
– rash, Commented Aug 18, 2018 at 19:42

Barmar · Accepted Answer · 2018-08-17 22:34:17Z

2

You need to escape the square brackets that should be treated literally. You didn't escape all the ^ characters, and you had an extra ^ character.

Also, {1,} can be written as +.

SELECT
  REGEXP_REPLACE('[XYZ^abc^0^1][dfgf^fgfgf^0^0][ggfgf^ererer^0^0][XYZ^abc^20^1][mkkfg^oorjj^0^0][XYZ^abc^0^0]',
                 '\[XYZ\^abc\^[[:digit:]]+\^[[:digit:]]+\]'
                 ) "REGEXP_REPLACE" from dual

answered Aug 17, 2018 at 22:34

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

Oracle regular expression REGEXP_REPLACE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related