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.
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
Smith
Sally
Jones
Steve
Brown
Allan
Cooper
Thompson
Peter
Manson
Now, we run the UPDATE statement to change all occurrences of ‘S’ to ‘W’.
FIRST_NAME
LAST_NAME
Smith
Wally
Jones
Wteve
Brown
Allan
Cooper
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:
Nested REPLACE statements
Using REGEXP_REPLACE
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;
SELECT TRANSLATE(whole_string, '[0-9]#$&&!_','[0-9]')
FROM dual;
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.
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..
Reply
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
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 ) || ‘”}’;
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.
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.
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.
Reply
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!
