Oracle REPLACE() Function

Oracle REPLACE() is a built-in function that replaces all occurrences of a specified substring in a string with a new substring.

If you need to replace based on a regular expression pattern match, use REGEX_REPLACE().

Oracle REPLACE() Syntax

Here is the syntax for the Oracle REPLACE() function:

REPLACE(char, search_string [, replacement_string ])

Parameters

char

Required. The original string.

search_string

Required. The substring to be replaced. It can be any of the following data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

replacement_string

Required. The new substring to replace with. It can be any of the following data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Return Value

The Oracle REPLACE() function returns a string with all occurrences of search_string in the source string replaced with replacement_string.

If search_string is null, REPLACE() returns the source string.

If replacement_string is null, REPLACE() removes all occurrences of search_string from the source string.

If the first parameter is NULL, REPLACE() returns NULL.

Oracle REPLACE() Examples

Here are some examples demonstrating the usage of the Oracle REPLACE() function.

Basic Example

SELECT
    REPLACE('Hi Alice', 'Hi', 'Hello') Result
FROM dual;

Output:

RESULT
______________
Hello Alice

In this example, we replaced the substring Hi with Hello.

Case Sensitivity

The MariaDB REPLACE() function performs a case-sensitive search.

SELECT
    REPLACE('Hi Alice', 'hi', 'Hello') Result
FROM dual;

Output:

RESULT
___________
Hi Alice

In this example, since hi is lowercase and does not match Hi, it was not replaced.

Multiple Matches

The MariaDB REPLACE() function replaces all matching occurrences, like this:

SELECT
    REPLACE('A small dog and a big dog', 'dog', 'cat') Result
FROM dual;

Output:

RESULT
____________________________
A small cat and a big cat

Removing Substrings

If you need to remove a substring from the source string, you can omit the third parameter or pass an empty string or NULL to the third parameter.

SELECT
    REPLACE('A small dog and a big dog', 'dog') Result1,
    REPLACE('A small dog and a big dog', 'dog', '') Result2,
    REPLACE('A small dog and a big dog', 'dog', NULL) Result3
FROM dual;

Output:

RESULT1                RESULT2                RESULT3
______________________ ______________________ ______________________
A small  and a big     A small  and a big     A small  and a big

NULL Parameters

If the first parameter is NULL, REPLACE() returns NULL.

SET NULL 'NULL';
SELECT
    REPLACE(NULL, 'A') Result1,
    REPLACE('A', NULL) Result2,
    REPLACE('A', 'B', NULL) Result3
FROM dual;

Output:

RESULT1    RESULT2    RESULT3
__________ __________ __________
NULL       A          A

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle REPLACE() is a built-in function that replaces all occurrences of a specified substring in a string with a new substring.