replace chages the record in database.

but translate won't change it the database content is not changed

Both Replace and Translate are single row functions in Oracle 9i.

The Replace Function replaces single character with multiple characters.

But in Translate Function replaces sinlge character with sinlge character only.

TRANSLATE function:

Translate function does character by character substitution in a string

format- TRANSLATE(STRING IF THEN)

Translate looks at each character in 'STRING' and then check 'IF' to see if that character is there if it is there then it notes the position in 'IF' where it found the character and then looks the same position in 'THEN'

for example-1. SELECT TRANSLATE(7671234 234567890 'BCDEFGHIJ') FROM dual;

Result of the query above is- GFG1BCD

2. SELECT TRANSLATE('NOW VOWELS ARE UNDER ATTACK' 'TAEIOU' 'Taeiou') FROM dual;

Result- NoW VoWeLS aRe uNDeR aTTaCK

this feature of TRANSLATE ability to eliminate characters from a string can prove very useful in cleaning up data.

one more example

SELECT amountchar TRANSLATE(amountchar '1 $' '1') FROM comma;

result:

AMOUNTCHAR TRANSLATE(AMOUNTCHAR....)

$0 0

$0.25 0.25

$1.25 1.25

$12.25 12.25

$1 234.25 1234.25

3. SELECT AMOUNTCHAR TRANSLATE(AMOUNTCHAR ' s' ' ') FROM dual;

AMOUNTCHAR TRASLATE(AMOUNTCHAR' '')

$0

$0.25

$1.25

$12.25

Without at least one real character in the 'THEN' TRANSLATE produces nothing.

------------------------------------------------------------------------

REPLACE

REPLACE function replaces a character or characters in a string with zero or more charcters

REPLACE('ADAH' 'A' 'BLAH')

this evaluate the string 'ADAH'.

Everywhere an 'A' is found it will be replaced with a string 'BLAH'

so the result will be- BLAHDBLAHH

2.REPLACE('GEORGE' 'GE' NULL)

result- OR

---- Courtesy: Oracle Complete Reference Book