René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

replace [Oracle SQL]

replace('orig-string', 'searched-string');
replace('orig-string', 'searched-string', 'replaced-string)
drop table replace_table;

create table replace_table (
  str   varchar2(30),
  find  varchar2(10), 
  subst varchar2(10)
);

insert into replace_table values ('ABC DEF GHI', 'DEF'  , 'bla' );
insert into replace_table values ('I am $name' , '$name', 'Rene');
insert into replace_table values ('Moo Foo Doo', 'oo'   , 'XX'  );
insert into replace_table values ('Hello World', 'World', null  );

column R format a30
select str, find, subst, replace(str, find, subst) R from replace_table;
STR                            FIND       SUBST      R
------------------------------ ---------- ---------- ------------------------------
ABC DEF GHI                    DEF        bla        ABC bla GHI
I am $name                     $name      Rene       I am Rene
Moo Foo Doo                    oo         XX         MXX FXX DXX
Hello World                    World                 Hello
In 10g, there is a regular expression version of replace: regexp_replace.