Oracle REPLACE function

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.

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