Live ORACLE

rem -----------------------------------------------------------------------
rem Filename:   strreplace.sql
rem Purpose:    Replace all occurences of a substring with another substring
rem Date:       28-Jul-2003
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

create or replace function strreplace(str varchar2, from_str varchar2, to_str varchar2)
  return varchar2
AS
  str_temp varchar2(4000);
  str_pos  number := instr(str, from_str);
BEGIN
  str_temp := str;
  while ( str_pos > 0 ) loop
    str_temp := substr(str_temp, 0, str_pos-1) || to_str ||
                substr(str_temp, str_pos + length(from_str));
    str_pos  := instr(str_temp, from_str);
  end loop;
  return str_temp;
END;
/
show errors


-- Examples

select strreplace('This is a beautiful day!', 'beautiful', 'horrible')
from dual
/

select 'mv '||name||' '||strreplace(name, 'OLDSID', 'NEWSID')
from   v$datafile
/

Replace all occurences of a substring with another substring