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 /