Oracle REPLACE function

The Oracle/PLSQL REPLACE function replaces a sequence of characters in a string with another character set.

ORACLE/PLSQL REPLACE FUNCTION SYNTAX

REPLACE( string1_id, string_to_replace_id, [ replacement_string_id ] )

Parameters and function arguments

• string1_id — a string to replace a sequence of characters with another character set.
• string_to_replace_id — string that will be searched in string1.
• replacement_string_id — is not obligatory. All occurrences of string_to_replace will be replaced by string_to_replace in string1. If the string_to_replace parameter is omitted, the REPLACE function will simply delete all occurrences of string_to_replace and return the resulting string.

The REPLACE function returns a string value.

THE REPLACE FUNCTION CAN BE USED IN THE FOLLOWING VERSIONS OF ORACLE/PLSQL

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

Let’s consider some examples of the REPLACE function and learn how to use the REPLACE function in Oracle/PLSQL.

SQL> SELECT REPLACE('123123abcd', '123') FROM DUAL;

--Result: abcd

SQL> SELECT REPLACE('123abcd123', '123') FROM DUAL;

--Result: abcd

SQL> SELECT REPLACE('222abcd', '2', '3') FROM DUAL;

--Result: 333abcd.

SQL> SELECT REPLACE('0000123', '0') FROM DUAL;

--Result: 123

SQL> SELECT REPLACE('0000123', '0', ' ') FROM DUAL;

--Result: 123