FB

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:

REPLACE ( 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.

Want an Oracle SQL function cheat sheet for easy referenceClick here to get a PDF file that contains all of Oracle functions for you to reference or print out.

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.

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

Result:

ORIGINAL_STRING REPLACE_STRING
Today is the 26th of February, 2015 TodXy is the 26th of FebruXry, 2015

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

 

Example 2

This is another simple REPLACE example.

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

Result:

ORIGINAL_STRING REPLACE_STRING
Today is the 26th of February, 2015 Today is probably the 26th of February, 2015

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.

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

Result:

ORIGINAL_STRING REPLACE_STRING
Today is the 26th of February, 2015 Today is e 26 of February, 2015

This string now just removes the occurrences of ‘th’.

 

Example 4

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

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

Result:

ORIGINAL_STRING REPLACE_STRING
Today is the 26th of February, 2015 Today is the 26th of February, 2015

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.

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

Result:

ORIGINAL_STRING REPLACE_STRING
Today is the 26th of February, 2015 (null)

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.

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

Result:

ORIGINAL_STRING REPLACE_STRING
Today is the 26th of February, 2015 Today is the 26th of February, 2015

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.

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

Result:

ORIGINAL_STRING REPLACE_STRING
Today is the 26th of February, 2015 Today is the 26th of February, 2015

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’.

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

Result:

ORIGINAL_STRING REPLACE_STRING
(null) (null)

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

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

Result:

ORIGINAL_STRING REPLACE_STRING
My first name is (x), my last name is (y), and I am from (z). My first name is John, my last name is Smith, and I am from England.

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.

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

Result:

ORIGINAL_STRING REPLACE_STRING
What’s the date today? What”s the date today?

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.

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

Result:

ORIGINAL_STRING REPLACE_STRING
What’s the date today? Whats the date today?

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

SELECT first_name, last_name
FROM customers;

Result:

FIRST_NAME LAST_NAME
John Smith
Sally Jones
Steve Brown
Mark Allan
Adam Cooper
Josh Thompson
Peter Manson

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

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

Now, let’s check the customers table again.

SELECT first_name, last_name
FROM customers;

Result:

FIRST_NAME LAST_NAME
John Smith
Wally Jones
Wteve Brown
Mark Allan
Adam Cooper
Josh Thompson
Peter Manson

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:

SELECT
REPLACE(
  REPLACE(
    REPLACE('My first name is (x), my last name is (y), and I am from (z).', '(x)', first_name),
    '(y)', last_name),
  '(z)', country)
FROM 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.

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

or

SELECT TRANSLATE(whole_string, '[0-9]#$&&!_','[0-9]')
FROM 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.

UPDATE table
SET 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.


Image courtesy of digitalart / FreeDigitalPhotos.net

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

8 thoughts on “Oracle REPLACE Function Usage, Tips & Examples”

  1. Mohan Vadivel

    hi,
    i have a query on this.. if i have two lines.. how to check and replace both the lines at a time with some other value.. could you please help on this..

    replace(filedname,’dogcat’,’****’) dog is have first line and cat string is sencond line..

    1. Hi,
      From what I understand, you want to get all of the values on more than one line into a single value, and then perform a REPLACE on it?
      First, you can get all values using the LISTAGG function:

      SELECT LISTAGG(animal) WITHIN GROUP (ORDER BY animal) AS animal_list
      FROM your_table;

      Just replace “animal” with the column that contains “dog” and “cat”, and your table name. Add a WHERE clause if needed.

      This should result in a single value of “catdog”.

      Then, you can perform a REPLACE on it:

      SELECT REPLACE(filedname, LISTAGG(animal) WITHIN GROUP (ORDER BY animal),’****’) FROM your_table

      Does that give you the result you’re after?

      Ben

  2. Hi Ben,
    I would like to know if I have to create a procedure that I have to put in 171 reclace, samething like to_char(replace(replace(replace(replace(replace(replace(replace(replace(replace( asciistr( replace(:new.OBSERVACAO,””,”””) ), ”, ‘u’ ), ‘”‘, ‘”‘ ), ‘u005C’, ‘\’ ), ‘/’, ‘/’ ), chr(8), ‘b’ ), chr(12), ‘f’ ), chr(10), ‘n’ ), chr(13), ‘n’ ), chr(9), ‘t’ )) end ) || ‘”,”IDLOGIN”:”‘ || ( case when :new.IDLOGIN is null then ‘null’ else replace(to_char(:new.IDLOGIN), ‘,’, ‘.’) end ) || ‘”,”DATALOG”:”‘ || ( case when :new.DATALOG is null then ‘null’ else to_char(:new.DATALOG, ‘yyyymmddhh24miss’) end ) || ‘”,”STATUS”:”‘ || ( case when :new.STATUS is null then ‘null’ else to_char(replace(replace(replace(replace(replace(replace(replace(replace(replace( asciistr( replace(:new.STATUS,””,”””) ), ”, ‘u’ ), ‘”‘, ‘”‘ ), ‘u005C’, ‘\’ ), ‘/’, ‘/’ ), chr(8), ‘b’ ), chr(12), ‘f’ ), chr(10), ‘n’ ), chr(13), ‘n’ ), chr(9), ‘t’ )) end ) || ‘”,”IDLOGINLOG”:”‘ || ( case when :new.IDLOGINLOG is null then ‘null’ else replace(to_char(:new.IDLOGINLOG), ‘,’, ‘.’) end ) || ‘”}’;

    this could be a trouble in my data base?

    1. Hi, that looks like quite a long statement. Is there a way you can do it without so many nested REPLACE statements? It might cause a performance issue if it’s called a lot.

  3. Hello Ben

    Newbie SQL coder here. Question on REPLACE. I have a case where I can not update the SQL table I’m pulling data from. There are CHR(09) values (tabs) in that data. I’d like to replace the CHR(09) with a space or have it be null when the row is selected. There are multiple columns within the rows that contain the CHR(09). I’m not sure how to build the select to do this.

    Select * from db.table when conditions

    I’ve tried various selects: For example: SELECT REPLACE(*, CHR(09), ‘ ‘) …… but these lead to syntax errors.

    There are several conditions on the WHEN and I want any row returned when true. I’d like the CHR(09) nulled or replaced as part of the select. Not all rows selected will contain CHR(09) characters. Is that possible?

    The problem I’m running into is that once the data is selected, I download it to the pc as a csv (I can’t control the data from being a csv), and the data in the csv gets corrupted due to the CHR(09) values in the rows.

    1. Hi Bob, sounds like a tricky problem!
      I think you’ll need to use the REPLACE on each column, rather than REPLACE(*) So your SELECT clause may look like this:
      SELECT REPLACE(firstname, CHR(09), ‘ ‘) AS firstname, REPLACE(some_other_column, CHR(09), ‘ ‘) AS some_other_column, another_column, next_column
      FROM…
      I’ve just used sample column names, which you can update with the real column names. The “AS firstname” after each column will mean the output has a heading of “first_name” instead of the function name.

      The WHERE clause should be OK. If you want any row returned when true, your WHERE clause may look like this:
      WHERE (condition_1 OR condition_2 OR condition_3)
      You can replace each of those conditions with what you’re testing, for example:
      WHERE (emp_status = ‘A’ OR num_transactions > 10 OR location = ‘AB’)
      Hope this helps! Feel free to respond if you have any questions.
      Ben

      1. Hello Ben!

        Thank you for the response. And dang! So, in this case, as I want all the columns, but need to use the REPLACE (or something similar), and I can’t wildcard the REPLACE, the select will need to list each column. Oh well. Thank you for the syntax!

  4. I have two strings of 10 char. I want to replace 2 or 3 char of some position of one string by 2 or 3 char of other string. How can I do it in PL/SQL?

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.