Changes remain permanently for replace?

Can any one tell me the difference between replace function & translate function of Oracle?

I read somewhere that replace makes the changes in database permanent where translate does not, is it correct?

Can any one show the query which will prove that r开发者_如何学JAVAeplace does changes permanently in database?

---

The difference between REPLACE and TRANSLATE:

• REPLACE substitutes one string for another string.
  
• TRANSLATE substitutes characters in one set for those in another.
• Neither TRANSLATE or REPLACE update the database.

Here's some examples:

create table nada (id number(8), description nvarchar2(30));
insert into nada values (1, "I like peanuts.");
insert into nada values (2, 'Peanuts are not a nut.');
insert into nada values (3, 'peanuts are a legume.');
commit;
select * from nada;

Id Description
-- ------------------------
 1 I like peanuts.
 2 Peanuts are not a nut.
 3 peanuts are a legume.

REPLACE works like this:

select replace(description, 'peanuts', 'tomatoes') from nada;

gives:

replace(description,'peanuts','tomatoes')
--------------------------------------
I like tomatoes.
Peanuts are not a nut.
tomatoes are a legume.

Just the word 'peanuts' is replaced with 'tomatoes'. It's case sensitive so 'Peanuts' isn't replaced.

TRANSLATE works like this:

select translate(description, ' .', '_!') from nada;

translate(description, ' .', '_!')
----------------------------------
I_like_peanuts!
Peanuts_are_not_a_nut!
peanuts_are_a_legume!

All spaces are changed to underscores and all periods are changed to bangs!

---

Neither TRANSLATE or REPLACE update the database. They just change the value in a statement. Of course, you can use that value to update the database if you want. For example:

select replace(description, 'peanuts', 'tomatoes') from nada;
select * from nada;

The table is still the same:

Id Description
-- ------------------------
 1 I like peanuts.
 2 Peanuts are not a nut.
 3 peanuts are a legume.

If you want to change the table, put it in an UPDATE or INSERT statement, such as:

update nada set description = replace(description, 'peanuts', 'tomatoes');
commit;

Now it's changed:

select * from nada;

Id Description
-- ------------------------
 1 I like tomatoes.
 2 Peanuts are not a nut.
 3 tomatoes are a legume.

Now if you want to fix 'Peanuts' too, you can use REGEXP_REPLACE.

---

Neither function makes permanent changes -- only when using an UPDATE, and after committing the transaction, is a "permanent" change made... Until the next update statement is run on that column ;)

The two functions operate differently:

SELECT translate('1tech23', '123', '456') AS translate,
       replace('1tech23', '123', '456') AS replace
  FROM DUAL

...returns:

translate  replace
-------------------
4tech56    1tech23

REPLACE replaces the provided pattern; TRANSLATE iterates over the string to process the replace character at a time (moving left to right)