Oracle REGEXP_REPLACE string to replace 'n' times starting with nn position

Question

I want to replace '|' with '_'. The replacement should start from nnth character and replace n times. For e.g.

ABC|1234|mno|p|q|r|456|XYZ|QRS|TUV ====> ABC|1234|mno|p_q_r|456|XYZ|QRS|TUV

In above example nn=14 and n=3

So far, I've tried this but not getting the expected results

SELECT REGEXP_REPLACE('ABC|1234|mno|p|q|r|456|XYZ', '[|]', '_',14) rep_str FROM DUAL

Answer 1

You can do it with plain substr/instr, but need to process edge cases carefully. Extract the part you need and replace all pipes in it. Then put everything together back.

with
--
function replace_n(
  str in varchar2,
  start_ in number,
  count_ in number
)
return varchar2
as
begin
  return
    /*Starting part unchanged*/
    substr(str, 1, start_)
    /*Replacement: locate n'th - 1 occurrence of pipe*/
    || translate(
      substr(str, start_ + 1, instr(str, '|', start_, count_-1) - start_)
      , '|'
      , '_'
    )
    /*Remaining string*/
    || substr(str, instr(str, '|', start_, count_ - 1) + 1)
  ;
end;
--
a(a) as (
  select
  'ABC|1234|mno|p|q|r|456|XYZ|QRS|TUV'
  from dual
)

select replace_n(a, 14, 3) as res
from a

| RES                                |
| :--------------------------------- |
| ABC|1234|mno|p_q_r|456|XYZ|QRS|TUV |

db<>fiddle here

UPD: Or if you were about replacement in the substring of size n starting from position nnn:

with
--
function replace_n(
  str in varchar2,
  start_ in number,
  count_ in number
)
return varchar2
as
begin
  return
    /*Starting part unchanged*/
    substr(str, 1, start_)
    /*Replacement: extract substring on size n*/
    || translate(
      substr(str, start_ + 1, instr(str, '|', start_, count_-1) - start_)
      , '|'
      , '_'
    )
    /*Remaining string*/
    || substr(str, instr(str, '|', start_, count_ - 1) + 1)
  ;
end;
--

db<>fiddle here