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

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

CodePudding user response：

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

CodePudding user response：

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;