Oracle REPLACE Function: A Simple Guide

When you need to do a simple "find and replace" on a string, the Oracle REPLACE function is the perfect tool for the job.

Unlike REGEXP_REPLACE, which searches for complex patterns, the REPLACE function searches for an exact, literal string and replaces every instance of it with another string.

This is extremely useful for:

• Correcting a common spelling mistake in a data column.
• Replacing a placeholder value with a real one.
• Completely removing a specific word from a string.

REPLACE Function Syntax

The syntax for the REPLACE function is very straightforward:

REPLACE(char, search_string, [replacement_string])

Let's break that down:

• char: The original string or column you want to search (e.g., 'Hello World' or product_name).
• search_string: The exact text you are looking for (e.g., 'World'). This is case-sensitive.
• [replacement_string] (Optional): The text you want to put in its place (e.g., 'Everyone').
  • If you omit this argument, REPLACE will simply remove all occurrences of the search_string.

Oracle REPLACE Function Examples

Here are two practical examples of how to use REPLACE.

Example 1: Replacing One String with Another with REPLACE

Imagine you have a product description, and you want to replace the word "Standard" with "Basic" to make it clearer for customers.

Query:

SELECT 
  REPLACE('Our new Standard Package is the best Standard deal.',
          'Standard',
          'Basic') AS "Updated Description"
FROM DUAL;

Result:

Updated Description
----------------------------------------------
Our new Basic Package is the best Basic deal.

Notice that it replaced every occurrence of "Standard".

Example 2: Removing a String Completely using REPLACE

Now, let's say you have a list of prices stored as text, and you want to remove the dollar sign $ so you can treat them as numbers.

You can do this by specifying $ as the search_string and simply omitting the replacement_string.

Query:

SELECT 
  REPLACE('$500.00', '$') AS "Cleaned Price"
FROM DUAL;

Result:

Cleaned Price
-------------
500.00