Oracle Replace Function

In this tutorial, we are going to learn how to use the replace() function in Oracle to replace all occurrences of a substring with another set of characters.

Oracle Replace() Function Syntax

The following code shows the syntax of the replace() function:

REPLACE(source_string, substring, replacement_string);

The function accepts three parameters:

source_string – defines the source string to be searched.
Substring – defines the substring that is to be replaced.
replacement_string – establishes the string or set of characters that are substituted in the place of the substring. This is an optional parameter. If the value of the replacement_string parameter is missing, the function removes all the occurrences of the substring from the source string.

Similarly, if the substring is empty, the function does nothing and returns the source_string.

The function returns a string type with all the occurrences of the substring replaced with the replacement_string.

Oracle Replace() Function Examples

The following examples demonstrate how the function behaves under various parameter and input types:

Example 1 – Replace Substring Occurrence

The example below illustrates the primary usage of the replace() function with all the input parameters provided.

select replace('Oracle database development', 'ab', 'xy') AS replaced
from dual;

The query above uses the replace() function to substitute the characters (ab) with (xy). Resulting output:

REPLACED |
---------------------------+
Oracle datxyase development|

Example 2 – Using the Replace Function to Remove a Substring

As mentioned, we can use the replace() function to remove a substring from the source string. This occurs when we do not provide the value from the substring as shown:

SELECT replace('https://linuxhint.com', 'https://') AS d FROM dual;

Outcome:

D |
-------------+
linuxhint.com|

Example 3 – Replace Values in a Table

We often use the replace() function to replace values in a database table. Take the example table shown:

create table sample_data
(
id number,
first_name varchar2(50),
ip_address varchar2(20),
btc_address varchar2(50),
credit_card varchar2(50),
identifier varchar2(40),
constraint sample_pk primary key (id)
);
insert into sample_data (id, first_name, ip_address, btc_address, credit_card, identifier)
values (11, 'Wallas', '169.158.70.77', '1CNz5d1d5SC8SaR6dFSVihwztqYx5Fg77q', '4017955174552',
'26811d77-0a3a-4397-bc33-f7835f7c7ab9');
insert into sample_data(id, first_name, ip_address, btc_address, credit_card, identifier)
values (12, 'Ian', '148.190.10.178', '1ADxBV7n9JeDDcb8pL24J9wV54mcSRHdu7', '4017956704480827',
'a69fe590-bc1b-4001-8ff8-154bcdb5802d');
insert into sample_data (id, first_name, ip_address, btc_address, credit_card, identifier)
values (13, 'Pasquale', '150.86.18.140', '126hVKom2Foy9LEA6M4pUAT1h97c2rSD8B', '4017953296787867',
'34ac9385-9e1e-4d13-9537-c4eedb9f2c35');

Before update statement:

SELECT FIRST_NAME, IP_ADDRESS, CREDIT_CARD FROM SAMPLE_DATA sd;

We can use the replace() function to replace all occurrences of 4 in the credit_card column with a 5.

UPDATE SAMPLE_DATA SET CREDIT_CARD = REPLACE(CREDIT_CARD, '4', '5');

After the update statement:

In the resulting table, we can verify the values in the credit_card columns have been substituted from 4 to 5.

Conclusion

Through this tutorial, you understood the working of the replace() function in Oracle databases.