In Oracle, the REPLACE() function allows you to replace a part of a string with another string.

The function accepts three arguments: the string, the substring to replace, and the replacement string (to replace the substring with).

You can omit the replacement string, in which case, the function will remove all instances of the substring.

Syntax

The syntax goes like this:

REPLACE(char, search_string
        [, replacement_string ]
       )

Where char is the string, search_string is the substring to replace, and replacement_string is the string to replace that substring with.

Example

Here’s a basic example:

SELECT 
    REPLACE('Barrier Island', 'Island', 'Reef')
FROM DUAL;

Result:

Barrier Reef

In this case we replaced the substring Island with Reef.

Multiple Matches

If the string to be replaced occurs multiple times within the string, all occurrences are replaced:

SELECT 
    REPLACE('Black dogs and white dogs', 'dog', 'cat')
FROM DUAL;

Result:

Black cats and white cats

No Match

If the substring doesn’t occur in the string, REPLACE() returns the string unchanged:

SELECT 
    REPLACE('Barrier Island', 'to entry', 'Reef')
FROM DUAL;

Result:

Barrier Island

Remove a Substring

Omitting the third argument removes the substring from the string:

SELECT 
    REPLACE('Black dogs and white dogs', 'dogs')
FROM DUAL;

Result:

Black  and white 

If you look closely, you’ll notice that the spaces still remain, as we didn’t specify any spaces in the substring.

Here it is again with the space included:

SELECT 
    REPLACE('Black dogs and white dogs', ' dogs')
FROM DUAL;

Result:

Black and white

Case Sensitivity

The REPLACE() function performs a case-sensitive match:

SELECT 
    REPLACE('Barrier Island', 'island', 'reef')
FROM DUAL;

Result:

Barrier Island

In this example, the case didn’t match, and so the original string was returned unchanged.

Empty Strings

Here’s what happens when an empty string is passed for each given argument:

SELECT 
    REPLACE('', 'dog', 'cat') AS "1",
    REPLACE('Black dog', '', 'cat') AS "2",
    REPLACE('Black dog', 'dog', '') AS "3"
FROM DUAL;

Result:

   1            2         3 
____ ____________ _________ 
     Black dog    Black    

So in this case:

• Passing an empty string for the initial string returns an empty string.
• Passing an empty string for the second argument returns the original string.
• Passing an empty string for the third argument removes the string to be replaced from the string.

Space Character

An empty string is not the same as the space character.

Here’s what happens when we change the empty string to a space:

SELECT 
    REPLACE(' ', 'dog', 'cat') AS "1",
    REPLACE('Black dog', ' ', 'cat') AS "2",
    REPLACE('Black dog', 'dog', ' ') AS "3"
FROM DUAL;

Result:

   1              2          3 
____ ______________ __________ 
     Blackcatdog    Black     

Therefore, if the string is nothing but a space, then we can replace that with another string:

SELECT REPLACE(' ', ' ', 'cat')
FROM DUAL;

Result:

cat

Null Arguments

Passing null for each argument works like passing an empty string:

SET NULL 'null';
SELECT 
    REPLACE(null, 'dog', 'cat') AS "1",
    REPLACE('Black dog', null, 'cat') AS "2",
    REPLACE('Black dog', 'dog', null) AS "3"
FROM DUAL;

Result:

      1            2         3 
_______ ____________ _________ 
null    Black dog    Black    

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Missing Arguments

Calling REPLACE() without passing any arguments results in an error:

SELECT REPLACE()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REPLACE()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

Too Many Arguments

And passing too many arguments returns an error:

SELECT REPLACE('Cat', 'a', 'b', 'c')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REPLACE('Cat', 'a', 'b', 'c')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: