Create
rem-------------------------
rem Filename: strreplace.sql
rem Purpose: Replace all occurences of a substring with another substring
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
rem
rem Filename: strreplace.sql
rem Purpose: Replace all occurences of a substring with another substring
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
Use
SELECT strreplace('This is a beautiful day!', 'beautiful', 'horrible') FROM dual;
SELECT strreplace('This is a beautiful day!', 'beautiful', 'horrible') FROM dual;