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>