replace从指定位置开始替换指定字符串制定次数

create or replace function replace_override_fnc(srcstr   varchar2,
                                                oldstr   varchar2,
                                                newstr   varchar2,
                                                startpos number,
                                                repnum   number default null)
  return varchar2 as
  occur_cnt        number;
  repl_num         number;
  oldstr_occur_pos number;
  new_srcstr       varchar2(2000);
  stop_pos         number;
  final_srcstr     varchar2(2000);
  resstr_temp      varchar2(2000);
  head_srcstr      varchar2(2000);
  tail_srcstr      varchar2(2000);
  result_str       varchar2(2000);
begin
  --dbms_output.put_line('source:' || srcstr);
  --the position where oldstr first appeareance.
  select instr(srcstr, oldstr, 1, startpos)
    into oldstr_occur_pos
    from dual;
  --dbms_output.put_line('oldstr_occur_pos:' || oldstr_occur_pos);
  --the actually srcstr.
  select substr(srcstr, oldstr_occur_pos) into new_srcstr from dual;
  --dbms_output.put_line('new_srcstr:' || new_srcstr);
  --the times oldstr occur in srcstr.
  select (length(new_srcstr) - length(replace(new_srcstr, oldstr))) /
         length(oldstr)
    into occur_cnt
    from dual;
  --dbms_output.put_line('occur time:' || occur_cnt);
  --actually replace times.
  if repnum is null then
    repl_num := occur_cnt;
  else
    if occur_cnt < repnum then
      repl_num := occur_cnt;
    else
      repl_num := repnum;
    end if;
  end if;
  --dbms_output.put_line('repl_num:' || repl_num);
  --head string,no replace.
  select substr(srcstr, 1, oldstr_occur_pos - 1)
    into head_srcstr
    from dual;
  --dbms_output.put_line('head_srcstr:' || head_srcstr);
  --stop replace position.
  select instr(new_srcstr, oldstr, 1, repl_num + 1)
    into stop_pos
    from dual;
  --dbms_output.put_line('stop_pos:' || stop_pos);
  if stop_pos <> 0 then
    --the actually srcstr.
    select substr(new_srcstr, 1, stop_pos - 1) into final_srcstr from dual;
    --dbms_output.put_line('final_srcstr:' || final_srcstr);
    --tail string,no replace
    select substr(new_srcstr, stop_pos) into tail_srcstr from dual;
    --dbms_output.put_line('tail_srcstr:' || tail_srcstr);
  else
    select substr(new_srcstr, 1) into final_srcstr from dual;
    --dbms_output.put_line('final_srcstr:' || final_srcstr);
    tail_srcstr := '';
    --dbms_output.put_line('tail_srcstr:' || tail_srcstr);
  end if;
  --replace result
  select replace(final_srcstr, oldstr, newstr) into resstr_temp from dual;
  --dbms_output.put_line('resstr_temp:' || resstr_temp);
  --result string
  select head_srcstr || resstr_temp || tail_srcstr
    into result_str
    from dual;
  --dbms_output.put_line('result:' || result_str);
  return result_str;
end;

 

SQL> select replace_override_fnc('china,chinese,japan,usa,china-beijing,beijing-china','i','III',3) from dual;

REPLACE_OVERRIDE_FNC('CHINA,CH
--------------------------------------------------------------------------------
china,chinese,japan,usa,chIIIna-beIIIjIIIng,beIIIjIIIng-chIIIna

SQL> select replace_override_fnc('china,chinese,japan,usa,china-beijing,beijing-china','i','III',3,1) from dual;

REPLACE_OVERRIDE_FNC('CHINA,CH
--------------------------------------------------------------------------------
china,chinese,japan,usa,chIIIna-beijing,beijing-china

SQL> select replace_override_fnc('china,chinese,japan,usa,china-beijing,beijing-china','i','III',3,10) from dual;

REPLACE_OVERRIDE_FNC('CHINA,CH
--------------------------------------------------------------------------------
china,chinese,japan,usa,chIIIna-beIIIjIIIng,beIIIjIIIng-chIIIna