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

Issue

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

Solution

In your simple example it’s easier to specify both 2 occurences:

  regexp_replace(
      str
     , '\|([^|]+)'
     ||'\|([^|]+)' -- 2 times just to make it more readable
     ||'(.*)'      --  others
     ,'_\1_\2\3'
     ,14
  )

Full example with test data: DBFiddle

with t as (
select
  'ABC|1234|mno|p|q|r|456|XYZ|QRS|TUV' str
 ,'ABC|1234|mno|p_q_r|456|XYZ|QRS|TUV' chk 
from dual
)
select
  str,chk,
  regexp_replace(
      str
     , '\|([^|]+)'
     ||'\|([^|]+)' -- 2 times just to make it more readable
     ||'(.*)'      --  others
     ,'_\1_\2\3'
     ,14
  ) as str2
from t
/

Or if you make it more customizable and specify number of replacement easier, you can use simple inline pl/sql function with a loop like this: DBFiddle

with function regexp_replaces(
    source_char  varchar2
   ,pattern      varchar2
   ,replace_char varchar2
   ,position     int
   ,cnt          int
  ) return varchar2
as
  res varchar2(4000):=source_char;
begin
  for i in 1..cnt loop
    res:=regexp_replace(res,pattern,replace_char,position,1);
  end loop;
  return res;
end;
select
  str,chk,
  regexp_replaces(str,'\|','_',14,2) as str2
from t;

Answered By – Sayan Malakshinov

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)