Replace characters after a defined pattern with regexp_replace in oracle

Question

I want to replace single characters in a string with the function regexp_replace in oracle. The replacement in the string should start behind a defined pattern.

Example:

In the String "Heyho || HeyheyHo" i would replace all the "y" characters behind the pattern "||" with the character "i". The characters occuring before the pattern should be ignored.

String:

Heyho || HeyheyHo

String after replacement:

Heyho || HeiheiHo

Surely quite easy for you?

Answer 1

Here's a solution using regexp_replace. The 4th argument is the starting position. After some thought, I decided not to skip the '+2'. Don't be lazy and waste cycles testing characters you know are not the target character.

SQL> with tbl(str) as (
     select 'Heyho || HeyheyHo' from dual
   )
   select str before,
          regexp_replace(str, 'y', 'i', instr(str, '||')+2) after
   from tbl;

BEFORE            AFTER
----------------- -----------------
Heyho || HeyheyHo Heyho || HeiheiHo

SQL>