Oracle, replace string for REGEXP_REPLACE based on the matched expression

Question

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?

Wiktor Stribiżew · Accepted Answer · 2017-06-08 08:57:19Z

2

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

edited Jun 8, 2017 at 8:57

answered Jun 8, 2017 at 8:53

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

1 Comment

Gnqz Over a year ago

Regular expressions have never been my forte, thank you very much. It worked as a charm!

Collectives™ on Stack Overflow

Oracle, replace string for REGEXP_REPLACE based on the matched expression

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related