Purpose
REPLACE() replaces all occurrences of the substring that matches char2 in char1 with char3.
Syntax
REPLACE(char1,char2[,char3])
Parameters
| Parameter | Description |
|---|---|
| char1 | The source string. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| char2 | The substring to be replaced. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| char3 | The replacement string. The default value is NULL rather than a blank space, which mean that the substring will be removed. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. Note: If char3 is not specified or is NULL, all occurrences of char2 in char1 will be removed. If char2 is NULL, char1 is returned. |
Return type
The string returned is in the same character set as char1.
If
char1is of theLOBdata type, the return type isCLOB.If
char1is not of theLOBdata type, the return type isVARCHA2.
Examples
The following example replaces the character b in the string Oceanbase with B.
obclient> SELECT REPLACE('Oceanbase','b','B') "replace" FROM DUAL;
+-----------+
| replace |
+-----------+
| OceanBase |
+-----------+
1 row in set