Oracle REPLACE function.

Preamble

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

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

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​