CODEWITHSUNDEEP
regexoracle-database
Gnqz
Gnqz

Reputation: 3382

Oracle, replace string for REGEXP_REPLACE based on the matched expression

I want to repair some data, which was uploaded using incorrect encoding. Consider the following example:

RUE DE SAN MARTI¦O N¦ 123

I want to replace the ¦ with say #, but only in cases when it's preceeded by a number or the character N

My desired output is:

RUE DE SAN MARTI¦O N# 123

I tried the following replace:

SELECT REGEXP_REPLACE('RUE DE SAN MARTI¦O N¦ 123','[\d]\¦|[N]\¦','#')
  FROM dual;

which correctly detects the chacter to match, but from what I know replacement string is used as literal. However I want to preserve the N before ¦. Has anyone had any luck solving similar issue?

Upvotes: 1

Views: 485

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626845

You may use

([0-9N])¦

and replace with \1#.

See the regex demo

Details:

  • ([0-9N]) - a capturing group matching a digit or N (can be referred to with the \1 backreference from the replacement pattern)
  • ¦ - a literal symbol is matched.

See an Oracle online demo:

SELECT REGEXP_REPLACE('RUE DE SAN MARTI¦O N¦ 123','([0-9N])¦','\1#') AS Result FROM dual

enter image description here

Upvotes: 2

Related Questions