Oracle REPLACE function

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

Description

The REPLACE function is used to return char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. When search_string is null, then char is returned.

Syntax:

REPLACE(char, search_string
        [, replacement_string ]
       )

Parameters:

Name	Description	Data Type
char	A string	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB
search_string	A string, to be searched.	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB
replacement_string	A string, to be replaced.	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB

Return Value Type

CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB

Applies to

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Pictorial Presentation

Examples: Oracle REPLACE function

The following example replaces occurrences of M with F:

SELECT REPLACE('MAN and MAT','M','F') "New String"
 FROM DUAL;

Sample Output:

New String
-----------
FAN and FAT

Previous: REGEXP_SUBSTR
Next: RPAD