Replace oracle function

Learn how to replace matching string with another string using replace function with example queries.

REPLACE function replaces a search string with replacement string.

Syntax

REPLACE(string_expression, string_pattern ,string_replacement)

string_expression-is the string to be replaced.

string_pattern– is the string that will be searched for in string_expression.

string_replacement(optional)– All occurrences of string_pattern will be replaced with string_replacement in string_expression.
If the replacement_string is omitted, REPLACE removes all occurrences of string_to_replace and returns the resulting string.

Example queries

For the below query the sub-string oracle is replaced with null value as there is no replacement string is provided.

SELECT REPLACE('This is a oracle 11g and 12c substring expression', 'oracle')
 FROM dual;

Output
This is a 11g and 12c substring expression

For the below query the sub-string is replaced with ‘ORACLE PL-SQL’ for all the occurrences of the sub-string ‘oracle’

SELECT REPLACE('This is a oracle 11g and 12c substring expression', 'oracle','ORACLE PL-SQL')
 FROM dual;

Output

This is a ORACLE PL-SQL 11g and 12c substring expression

For the below query the sub-string is replaced with ‘b’ for all the occurrences of the sub-string ‘strong’.

 SELECT REPLACE('<strong>This is a oracle 11g and 12c substring expression </strong>', 'strong','b')
 FROM dual;

Output

<b>This is a oracle 11g and 12c substring expression </b>

For the below query replacement may happen more than once.

SELECT REPLACE('This is a oracle 11g and 12c substring expression', 'is ',' was ')
 FROM dual;

Output
Th was was a oracle 11g and 12c substring expression

For the below query replacing does not occur as string_pattern not present.

SELECT REPLACE('This is a oracle 11g and 12c substring expression', 'was','is')
  FROM dual;

Output
This is a oracle 11g and 12c substring expression