Thursday, April 17, 2014

Simple Example with REPLACE

The REPLACE function allows you to change a string of characters to another string of characters and can accept three parameters:
 
(1)    Input column name.
(2)    Old string value.
(3)    New string value.
 
You can see what I mean in the example below, which I tested on Oracle 11.2:
 
SQL> create table directory_name
  2  (location varchar2(30))
  3  /
 
Table created.
 
SQL> insert into directory_name
  2  values('/batch/prod/dir1')
  3  /
 
1 row created.
 
SQL> insert into directory_name
  2  values('/batch/prod/dir2')
  3  /
 
1 row created.
 
SQL> select location from directory_name
  2  /
 
LOCATION
------------------------------
/batch/prod/dir1
/batch/prod/dir2
 
SQL> update directory_name
  2  set location = replace(location,'prod','test')
  3  /
 
2 rows updated.
 
SQL> select location from directory_name
  2  /
 
LOCATION
------------------------------
/batch/test/dir1
/batch/test/dir2
 
SQL>

No comments: