Skip to Main Content
  • Questions

  • Replace all occurrence of a string with a new value

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: April 14, 2009 - 6:32 pm UTC

Last updated: April 15, 2009 - 3:08 pm UTC

Version: 10.2.0.3

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I need to replace all occurrence of a given string with a different value. The value could appear anywhere in the string and may appear several times.

For example:

CREATE TABLE TEST_REPLACE (
  ID              NUMBER(12)      NOT NULL PRIMARY KEY,
  NAME            VARCHAR2(128)   NULL
);

insert into test_replace values (1, 'SA-X04 Europe All Europe/Boating Business');
insert into test_replace values (2, 'Function Europe All Europe/Function');

commit;


What I want to do is replace all occurrence of Europe with EMEA.

So the above NAME value after the update would look like:

col type format a10
col name format a50
select * from test_replace;

        ID NAME                                               
---------- -------------------------------------------------- 
         1 SA-X04 EMEA All EMEA/Boating Business              
         2 Function EMEA All EMEA/Function


I could do this with code and RegEx, but I'm not so quite sure how I would do this with a single update statement. Is that possible?

Thanks!
-Peter

and Tom said...

ops$tkyte%ORA11GR1> select * from test_replace;

        ID NAME
---------- ------------------------------------------
         1 SA-X04 Europe All Europe/Boating Business
         2 Function Europe All Europe/Function
         3 Function new European data

ops$tkyte%ORA11GR1> update test_replace set name = replace(name,'Europe','EMEA');

3 rows updated.

ops$tkyte%ORA11GR1> select * from test_replace;

        ID NAME
---------- ------------------------------------------
         1 SA-X04 EMEA All EMEA/Boating Business
         2 Function EMEA All EMEA/Function
         3 Function new EMEAan data



a simple replace does that - however, I think you haven't specified your issue sufficiently, ID=3 demonstrates why I think that.


If you specify it better, more completely, we can probably do that one too... We might have to use a regexp for that because I think the specification will be:

set name =
regexp_replace(name,'(^|[[:space:]])Europe([[:space:]]|$|/)','\1EMEA\2')

(thanks to Tyler Muth for doing the regular expression for me :) )

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

Peter Tran, April 15, 2009 - 3:18 pm UTC

You're absolutely right. I didn't think about the issue of having the data I want replaced being embedded inside an existing word like your example #3 below.

I love Oracle's RegEx feature!!!

Why replace statement replace all rows

Ajay, January 06, 2014 - 12:51 pm UTC 

Hi Tom,

I have a quick question. I have a table with two columns. were I want to update only one row using replace function.
But when I use to to update, it updates all rows in table.

demonstration:

desc test            
 Name    Null?    Type
 ------- -------- --------------
 X                VARCHAR2(10)
 Y                VARCHAR2(10)

SQL> select * from test;

X          Y
---------- ----------
ajay       singh
ajay       singh
ajay       singh
ajay       singh
ajay       singh
ajay       singh
ajay       singh
ajay       singh
ajay       singh
ajay       singh
ajay       singh
raj        singh

SQL> update test set X=replace(X,'raj','ajay');

16 rows updated.

SQL> rollback;

Rollback complete.


SQL> update test set X=replace(X,'RAJ','ajay') where  X='raj';

1 row updated.


commit;

Questions:
1. Do replace function replaces all rows in table, or it find the pattern in and replace it.
2. Why we need to specifically write where clause to make replace function understand , which row we need to update.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.