Oracle REPLACE Function Usage, Tips & Examples

The Oracle REPLACE function is another string manipulation function within Oracle.

Learn how to use it and some common examples in this article.

In this article, you'll learn:

• What the Oracle REPLACE function is
• The syntax and parameters
• Several examples of this function and the output of each example
• Answers to some FAQ

What Is The Oracle REPLACE Function?

The Oracle REPLACE function is used to replace one text string with another.

The text string can be an entire value, or as little as a single character.

Syntax

The syntax of the Oracle REPLACE function is:

1REPLACE ( whole_string, string_to_replace, [replacement_string])

Parameters

The parameters of the REPLACE function are:

• whole_string (mandatory): This is the string that will be checked for the characters to be replaced. It is usually the longer of all the parameters.
• string_to_replace (mandatory): This is the string that will be searched for within whole_string.
• replacement_string (optional): This is the string that will be used to replace occurrences of string_to_replace. If this is not specified, then the Oracle REPLACE function just removes all occurrences of string_to_replace.

Refer to the examples below for more information.

Examples of the REPLACE Function

Here are some examples of the REPLACE function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This is a simple REPLACE example.

1SELECT
2'Today is the 26th of February, 2015' AS ORIGINAL_STRING,
3REPLACE('Today is the 26th of February, 2015', 'a', 'X') AS REPLACE_STRING
4FROM dual;

Result:

This replaces the character 'a' with an 'X', wherever it is found.

Example 2

This is another simple REPLACE example.

1SELECT
2'Today is the 26th of February, 2015' AS ORIGINAL_STRING,
3REPLACE('Today is the 26th of February, 2015', 'the', 'probably the') AS REPLACE_STRING
4FROM dual;

Result:

As you can see, it replaces the string 'the' with the string 'probably the'

Example 3

This example does not provide a parameter for the replacement_string.

1SELECT
2'Today is the 26th of February, 2015' AS ORIGINAL_STRING,
3REPLACE('Today is the 26th of February, 2015', 'th') AS REPLACE_STRING
4FROM dual;

Result:

This string now just removes the occurrences of 'th'.

Example 4

This example shows what happens when the string_to_replace is not found.

1SELECT
2'Today is the 26th of February, 2015' AS ORIGINAL_STRING,
3REPLACE('Today is the 26th of February, 2015', 'X', 'Y') AS REPLACE_STRING
4FROM dual;

Result:

As 'X' is not found, no operation to replace it with 'Y' is done.

Example 5

This example shows what happens when both the whole_string and string_to_replace are the same.

1SELECT 'Today is the 26th of February, 2015' AS ORIGINAL_STRING,
2REPLACE('Today is the 26th of February, 2015', 'Today is the 26th of February, 2015') AS REPLACE_STRING
3FROM dual;

Result:

As you can see, a value of NULL is returned.

Example 6

This example shows how to replace a carriage return character with a space.

1SELECT
2'Today is the 26th of February, 2015' || CHR(13) AS ORIGINAL_STRING,
3REPLACE('Today is the 26th of February, 2015' || CHR(13), CHR(13), ' ') AS REPLACE_STRING
4FROM dual;

Result:

It can be hard to see in this example, but using real data, this would remove the carriage return and replace it with a space.

Example 7

This example is similar to the above example, but shows how to replace a carriage return and line feed with a space.

1SELECT
2'Today is the 26th of February, 2015' || CHR(13) || CHR(10) AS ORIGINAL_STRING,
3REPLACE(
4  'Today is the 26th of February, 2015' || CHR(13) || CHR(10), CHR(13) || CHR(10),
5  ' ') AS REPLACE_STRING
6FROM dual;

Result:

It can be hard to see in this example, but using real data, this would remove the carriage return and line feed and replace it with a space.

Example 8

This example attempts to replace the NULL value with a value of 'x'.

1SELECT NULL AS ORIGINAL_STRING,
2REPLACE(NULL, NULL, 'x') AS REPLACE_STRING
3FROM dual;

Result:

As you can see, it doesn't work as expected. The better approach is to use NVL(NULL, 'x').

Example 9

This example uses nested REPLACE functions to replace multiple characters

1SELECT 'My first name is (x), my last name is (y), and I am from (z).' AS ORIGINAL_STRING,
2REPLACE(
3  REPLACE(
4    REPLACE('My first name is (x), my last name is (y), and I am from (z).', '(x)', 'John'),
5    '(y)', 'Smith'),
6  '(z)', 'England') AS REPLACE_STRING
7FROM dual;

Result:

This works as expected, but it's more efficient to use REGEXP_REPLACE.

Example 10

This example shows how to replace a single quote with a double quote. Notice the four single quotes in the string_to_replace parameter, and the single-double-single quotes in the replacement_string parameter.

1SELECT 'What''s the date today?' AS ORIGINAL_STRING,
2REPLACE('What''s the date today?', '''', '"') AS REPLACE_STRING
3FROM dual;

Result:

As shown, the single quote is replaced with a double quote.

Example 11

This example shows how to replace a single quote with nothing or to remove the single quote from the string.

1SELECT 'What''s the date today?' AS ORIGINAL_STRING,
2 REPLACE('What''s the date today?', '''') AS REPLACE_STRING
3 FROM dual;

Result:

The single quote is now removed from the string.

Example 12

This is an UPDATE statement used to update a column using the REPLACE function.

First, let's check the customers table

1SELECT first_name, last_name
2FROM customers;

Result:

Now, we run the UPDATE statement to change all occurrences of 'S' to 'W'.

1UPDATE customers
2SET first_name = REPLACE(first_name, 'S', 'W');
37 rows updated.

Now, let's check the customers table again.

1SELECT first_name, last_name
2FROM customers;

Result:

Two records have been updated (first name of "Wally" and "Wteve").

Oracle REPLACE Frequently Asked Questions

Can Oracle Replace a Carriage Return?

Sometimes you want to remove or replace carriage return or new line characters from within string values. This can occur if you are capturing user input and they press Enter to move to a new line, but you want to remove that from your query.

To do that, you need to use either a "carriage return", a "line feed", or both. They are done using the CHR function.

The carriage return is checked by using CHR(13), as the number 13 evaluates to the ASCII character for carriage return. Likewise, CHR(10) is a line feed character. Refer to the example below on how to replace a carriage return with this function.

Can Oracle Replace NULL With 0?

Yes, you can. If you want to replace NULL values with zero to use for calculations, for example, Oracle can do this. However, it's not done with the REPLACE function. It's done with the NVL function.

A NULL value can exist for a column, and it is the entire value in the column, not part of a larger value. So, if you had NULL in a column and wanted to replace it, a REPLACE(NULL, NULL, 'x') would not work as expected.

It's better to use the NVL function, such as NVL(value_to_check, 0).

Can Oracle Replace Multiple Characters?

It can't be done by itself, but there are a few ways it can be done:

1. Nested REPLACE statements
2. Using REGEXP_REPLACE
3. A custom function

Perhaps the most widely accepted way is to use nested REPLACE statements.

For example, consider this string:

"My first name is (x), my last name is (y), and I'm from (z)."

If you wanted to replace the (x), (y), and (z) values, you would use a nested REPLACE statement:

1SELECT
2REPLACE(
3  REPLACE(
4    REPLACE('My first name is (x), my last name is (y), and I am from (z).', '(x)', first_name),
5    '(y)', last_name),
6  '(z)', country)
7FROM customers;

As you can see, this can get quite messy if there are many strings to replace.

You can write a custom function to perform this, but that's outside the scope of this article.

My preferred way is to use REGEXP_REPLACE, which I believe is faster and easier to read. See the REGEXP_REPLACE function definition for examples.

Can Oracle Replace Special Characters?

Yes, but you can only use it to remove a single special character for each function.

You could use nested REPLACE statements, but if you're removing many special characters, this can get messy and slow.

There are two other ways to do this - using REGEXP_REPLACE or TRANSLATE.

1SELECT REGEXP_REPLACE(whole_string,'[^[:alnum:]'' '']', NULL)
2FROM dual;

or

1SELECT TRANSLATE(whole_string, '[0-9]#$&&!_','[0-9]')
2FROM dual;

See the guide to REGEXP functions and TRANSLATE function definition for more information.

Can Oracle Replace Single Quote?

Yes, it can.

You need to "escape" the single quote character from inside the string you specify. To do this, you need to specify it as four single quotes - two to contain the string, one as an escape character, and one as the single quote. Learn more about how to do this in this article.

The third parameter would be what you want to replace it with. This could be a double quote ('"'), no value (''), a space (' '), or something else.

Refer to the examples section below for some actual queries.

Can Oracle Replace a String In A Column?

Yes, it can. This is relatively simple to do.

You'll need to know both the string you're replacing and the string you're replacing it with. You would then use an UPDATE statement on the table, and refer to the column.

1UPDATE table
2SET column = REPLACE(column, 'abc', 'def')

This would replace all occurrences of 'abc' in the values in the column with a value of 'def' inside those values.

See the Examples section below for more information.

Similar Functions

Some functions which are similar to the REPLACE function are:

• SUBSTR - allows a smaller string to be extracted from a larger string.
• INSTR - checks one string for the occurrence of another string
• REGEXP_REPLACE - performs a similar function to REPLACE but uses regular expressions.
• TRANSLATE - similar to REPLACE but lets you perform several one-to-one replacements inside one function.

You can find a full list of Oracle functions here.