All Products
Search
Document Center

REPLACE

Last Updated: Jun 18, 2021

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 |
+------------+