Introduction: Oracle REPLACE Function — Complete Guide

The REPLACE function in Oracle is a simple yet powerful string manipulation tool used to substitute occurrences of a substring within a string.

Syntax

The basic syntax is quite simple:

REPLACE(source_string, search_string [, replacement_string])
	•	source_string      -- Original text
	•	search_string      -- Text to find
	•	replacement_string -- Text to replace with (optional)

If the replacement_string is omitted, Oracle removes the search_string from the source string.

Basic Examples

Simple Replacement

SELECT REPLACE('Hello World', 'World', 'Oracle') AS result FROM dual;

Output:

Hello Oracle

Lab output example:

Formatting Output

in this case, the systems setup will display the date information by using the . as separator:

But I would like to have ‘/ ‘ as separator! This can also be easily achieved by using the REPLACE function:

SELECT REPLACE(sysdate, '.', '/') AS formatted_date
FROM dual;
FORMATTED_
----------
01/05/2026

Removing Text

SELECT REPLACE('Hello World', 'World') AS result FROM dual;

Output:

Hello

Or:

SELECT REPLACE('Removing Text', 'Text') AS result
FROM dual;

Output:

Removing

Case Sensitivity

Oracle REPLACE is case-sensitive.

SELECT REPLACE('Hello World', 'world', 'Oracle') AS result
FROM dual;
Output:
Hello World

Or, lab example:

Practical Use Cases I have encountered in real-life Clients scenarios

Data Cleaning / scrubbing: (Remove Special Characters)

Suppose we loaded in a table a flat file presented as an external table but the file data is now corrupted by ‘-‘ characters! Our task is to remove the ‘-‘ characters from the telephone number fields.

Use case: Normalize phone numbers

like so:

123-456-7890 → 1234567890

The basic function call that would help us update the table is:

SELECT REPLACE(phone_number, '-', '') AS cleaned_phone
FROM customers;

In the lab example we start with a table containing corrupt phone numbers:

next step is to update the table data:

Commit and check the results:

Now the data in the phone number column is cleaned of the undesired characters.

Standardizing Text Values

In another situation, we received and uploaded without checking a set of files with a lot of country names that would not be according to desired standard. In our case, for example: ‘U.S .A’ and not ‘USA’! As a consequence, the wrong data would cause application unhandled exception crashes during validation! The fix on database side, is extremely easy by using the REPLACE function:

the table would look like so in the lab environment:

Checking how the REPLACE function would work:

SELECT REPLACE(country, 'U.S.A', 'USA') AS standardized_country
FROM customers;

We can see that we would get the desired result according to standards:

After running an update and checking again, the problem was easily solved:

update customers set country = REPLACE(country, 'U.S.', 'USA');

the result is now as expected!

Masking Sensitive Data

Another use case is masking sensitive data by using the replace function when ,for example when the code publishes some information in the application interface.

Here is an example:

Right now, I have the credit card information in my customers table:

select * from Customers;
COLUMN1              PHONE_NUMBER   COUNTRY    CRDITCARD#    
-------------------- -------------- ---------- --------------
Customer 1           1234567890     USA        58392017465028
New Customer         1234567890     USA        94173620581947

But I would like to mask their first 12 digits, I cna achieve that by using a combination of REPLACE function with SUBSTR:

select REPLACE(crditcard#, SUBSTR(crditcard#, 1, 12), '************') AS masked_cc
FROM customers
;
MASKED_CC                                                                                                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
************28
************47

Removing HTML Tags

Simple Case

Sometimes, we get upload files corrupted by an external application with a lot of HTML tags. In this case we have uploaded the file content in the description field. With a simple approach, the use of REPLACE function can help like so:

SELECT REPLACE(description, '<br>', '') AS clean_text
FROM products;

Complex HTML removal

The use REGEXP_REPLACE, can be more powerful in many cases, but I leave this topic to be covered in a future article.

Bulk Updates in Tables

The use of REPLACE function is very beneficial from performance point of view and easy to be implemented in the code.

Here is another example of changing the Old Company information to the New Company, for example after an acquisition:

The current table has the old email domain information:

But, after the migration the new domain should be used!

This can be updated in the database, by using REPLACE function:

UPDATE customers
SET email_domain = REPLACE(email_domain, 'oldcompany.com', 'newcompany.com');

Afterwards, the table has all the new company data:

COLUMN1              PHONE_NUMBER   COUNTRY    CRDITCARD#     EMAIL_DOMAIN   
-------------------- -------------- ---------- -------------- ---------------
Customer 1           1234567890     USA        58392017465028 newcompany.com 
New Customer         1234567890     USA        94173620581947 newcompany.com 

Using REPLACE in PL/SQL

is quite straightforward and performing well! We cal also use the variables defined in the code.

Here is an example:

set serveroutput on
DECLARE
    v_text VARCHAR2(100) := 'Oracle is powerful';
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_text);
    v_text := REPLACE(v_text, 'powerful', 'amazing');
    DBMS_OUTPUT.PUT_LINE(v_text);
END;
/

Nested REPLACE (Multiple Substitutions)

The functions can be nested as the result from call can be an argument for another call!

In this case we will remove the ‘-‘ character from the string and REPLACE A with X:

SELECT REPLACE(REPLACE('A-B-C', '-', ''), 'A', 'X') AS result
FROM dual;

REPLACE vs REGEXP_REPLACE

Here are a few subjective conclusions I have reached over time from using those two functions:

Example Comparison

Provided to highlight the case of use of the REPLACE function. we can also note the execution of this simple case is almost the same.

Using REPLACE:

SELECT REPLACE('abc123', '123', '') FROM dual;

Using REGEXP_REPLACE:

SELECT REGEXP_REPLACE('abc123', '[0-9]', '') FROM dual;

Performance Tip

Use REPLACE when:

• Exact match is known
• No pattern needed

Avoid chaining too many nested REPLACE calls → may reduce readability

Common Pitfalls

Treat Case Sensitivity with care!

Use UPPER when / if needed:

Note the even now, the letter cases of the word ‘Hello’ were changed in this example according to the UPPER function!

NULL Behavior

Be mindful of the NULL values and the results returned after a function call!

Empty Replacement

Always be mindful of the empty replacement cases and test them!

When NOT to Use only REPLACE

Avoid REPLACE function only when:

• You need pattern-based replacement → use REGEXP_REPLACE
• You need position-based edits → combine with the use of SUBSTR as shown above

Summary

• REPLACE is fast and simple for direct substitutions
• Works in both SQL and PL/SQL
• Best for data cleaning, formatting, and normalization
• Case-sensitive and does not support patterns (use REGEXP_REPLACE)