Oracle REPLACE Function: The Data Cleanup Magic

oracle replace function
Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print

Oracle provides a powerful set of functions for working with data. In such cases, the Oracle REPLACE function can be used on Data manipulation. Data manipulation is one of the fundamentals of data management, and it also has to show Information effectively and correctly.

This article discusses Oracle replace function and basic examples.

what is the replace function in Oracle

below is the Oracle replace function syntax and next shows what things can be done using the function.

-- Syntax
REPLACE(original_string, search_string, replacement_string)
  • original_string: This is the string we are planning to change.
  • search_string: finding string from the original_string.
  • replacement_string: New string to modify in the original_string.

Things can be done with the replace function.

  1. Removing Unwanted Characters
  2. Dealing with NULL Values
  3. Capitalizing Words
  4. Converting Date Formats
  5. Redacting Sensitive Information
  6. Handling Escape Characters

Data Cleaning and Formatting

Removing Unwanted Characters

If you request some information from the interface customer inputs need to be cleaned and polished before being saved into the database. Sometimes unexpected characters or symbols may be available in the text.

As an example name initial not hoping. (dot) between characters. But if a user entered such a symbol easy to replace the empty value using the Oracle replace function.

SELECT REPLACE(initials, '.', ' ') AS cleaned_initials FROM customer_data;

This is clean up and values without damaging information.

Dealing with NULL Values

NULL values are bound with all the types of parameters in the database. In some cases, we are not allowed to save null values or add default values in point before triggering some logic. In those kinds of situations, NULL values can be replaced by some default values using the Oracle replace function.

SELECT REPLACE(operation_type, NULL, 'N/A') AS formatted_name FROM products;

In this scenario, if operation_type is NULL, it will change to N/A to maintain the logic and consistency of the code.

Text Transformation

Capitalizing Words

This is a simple example of changing the string to capitalize the first letter of the input or text. This will help with formatting text.

SELECT INITCAP(REPLACE(input_value, ' ', '_')) AS formatted_description FROM product_descriptions;

Before the use of  INITCAP input needs to be changed to a single word for the use replace function to change the text

Converting Date Formats

This is one of the commonly used functionalities for changing the format of date values. Before creating a date need to set the value according to the input date format. Therefore create a valid input needs to set the value in the correct format.

SELECT TO_DATE(REPLACE(date_string, '/', '-'), 'DD-MM-YYYY') AS formatted_date FROM order_dates;


DATE converter expects the date in ‘DD-MM-YYYY’ format therefore change the backslash into to dash.

Query Enhancement

Redacting Sensitive Information

When showing the full or half of the information must be hidden. In those cases REPLACE function helps on change values into ‘*’:

SELECT REPLACE(customer_mobile, SUBSTR(customer_mobile, 4, 6), '******') AS redacted_ssn FROM customer_data;

In this example, middle numbers are hidden from information for security purposes.

Advanced Techniques

Handling Escape Characters

The escape character is a special character that needs to handle CHR below showing how to handle and replace the Escape character before saving.

SELECT REPLACE(description, CHR(9), ' ') AS formatted_description FROM descriptions;

CHR(9) represents the tab character.

Performance Considerations

Nowadays performance is hugely considerable. While the REPLACE function is a powerful tool, it needs to think about performance as well:


If you frequently use the Oracle REPLACE function in queries use the function base index on those columns. That will help to search and filter rows with converted values in the column.

Use WHERE Clauses

When replacing values limit the affected row by adding a condition. Then replacing time is reduced because of limited rows affected for replacement. Otherwise, we replace a lot of data that is not usable at the end.

Evaluate Alternatives

REGEXP_REPLACE is a great example of an alternative. If you are working with big-size values use the best function for replacing values. Replacement is not the best in every replacement situation.


The Oracle REPLACE function is a good tool for data manipulation in SQL queries. According to your requirement get the value of  REPLACE function that is simplifies complex tasks. This comprehensive article covers the Oracle REPLACE function from its basic usage to advanced techniques and performance considerations, providing readers with a thorough understanding of its capabilities.

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print

Leave a Comment

Your email address will not be published. Required fields are marked *

Related Articles
You May Like
Subscribe to our Newsletter
Scroll to Top