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>
