The REPLACE function replaces some identical strings in the value of a character expression with new strings.
Syntax
REPLACE(c1,c2[,c3])
Parameters
| Parameter | Description |
|---|---|
| c1 | The CHAR string to be replaced. |
| c2 | The string to be searched for and replaced. |
| c3 | The replacement string. By default, this parameter is empty. This indicates the deletion instead of using spaces. |
The data types of c1, c2, and c3 can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, and CLOB.
Return type
The character set of the returned string is the same as that of c1. If c3 is the default or NULL, all the occurrences of c2 in c1 are removed. If c2 is NULL, the result is c1. If the data type of c1 is CLOB, the function returns the CLOB data type. If the data type of c1 is not CLOB, the function returns the VARCHA2 data type.
Examples
Execute the following statement:
SELECT replace('he love you','he','i') test FROM DUAL;
The following query result is returned:
+------------+
| TEST |
+------------+
| i love you |
+------------+