CODEWITHSUNDEEP
regexoracle-database
rash
rash

Reputation: 11

Oracle regular expression REGEXP_REPLACE

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!

Upvotes: 1

Views: 92

Answers (1)

Barmar
Barmar

Reputation: 782499

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

Upvotes: 2

Related Questions