Oracle SQL: the Essential Reference

REPLACE

REPLACE(string, search_string [,replacement_string])

Searches a string and replaces one substring with another. This function also may be used to delete occurrences of a substring.

Parameters

string

Specifies the input string and the string to search.

search_string

Specifies the substring to search for.

replacement_string

Specifies the string you want to use in place of the search_string. If you omit this optional argument, all occurrences of search_string will be deleted.

Examples

SQL> SELECT REPLACE('The sky is blue.','blue','red') FROM dual;

REPLACE('THESKY
---------------
The sky is red.

SQL> SELECT REPLACE('The sky is blue red.',' red') FROM dual;

REPLACE('THESKYI
----------------
The sky is blue.