What is the Difference between Replace and Translate

Editorial / Best Answer

Answered by: Saumendra Mohanty

  • May 16th, 2006


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.

Showing Answers 1 - 40 of 40 Answers

gomathi.e

  • Mar 24th, 2006
 

replace chages the record in database.

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

  Was this answer useful?  Yes

Saumendra Mohanty

  • May 16th, 2006
 

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.

oradev03

  • Sep 14th, 2006
 

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

dl_mstr

  • Feb 12th, 2008
 

Translate replaces by position, the first character of the list to match is replaced by the first character of the replacement list. The second character with the second, and if there are characters in the list to match that do not have positional equivalents in the replacements list they are dropped.
Replace replaces the string to match with the replacement string. The replacement of a single character is the same as that of TRANSLATE.

dl_mstr

  • Feb 12th, 2008
 

Translate replaces by position, the first character of the list to match is replaced by the first character of the replacement list. The second character with the second, and if there are characters in the list to match that do not have positional equivalents in the replacements list they are dropped.

Replace replaces the string to match with the replacement string. The replacement of a single character is the same as that of TRANSLATE.

For Example:

SQL> SELECT translate('So What', 'o', 'ay')
  2  FROM dual;

TRANSLATE
-------
Sa What

SQL> SELECT REPLACE('So What', 'o', 'ay')
  2  FROM dual;

REPLACE
--------
Say What

ashishdixit

  • May 27th, 2008
 

below is an eg showing the difference

SQL> select replace('missisippi','is','12') replace, translate('missisippi','is','12') translate from dual;

REPLACE               TRANSLATE
----------                  ----------
m12s12ippi          m122121pp1

hope it clarifies the doubt!

rkhreddy

  • Sep 23rd, 2008
 

if we write query like this using traslate function
SYNTAX:-
              transalte(string,char which u want replace with,)
Suppose for the EMPLOYEE table....
 SQL>SELECT TRASLATE   
             (upper(ENAME),
             'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
             'EFGHIJKLMNOPQRSTUVWXYZABCD')
            FROM EMP
  --     THIS CHARCTER REPLACED BY   --     
 A        REPLACED BY        E
B                ''                    F
C                "                    G
 |                                       |
 |                                       | 
 |                                       |
/                                     /

Z                                      D

every character that is present in the employee names in the emplooyee table
suppose name is
PAULL        --------->TEYPP
LIKE  this every chat is replaced by its corresponding character
 We use traslate function inthe encryption of the data ...

  Was this answer useful?  Yes

replace function replaces a sequence of characters in a string with another set of characters.
ex.

 select replace('123data',123,'X') from dual;


REPLA
-----
Xdata

here 123 replaces by X

translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time.
ex1.
select translate('1data23base',123,'XYZ') from dual

TRANSLATE('
-----------
XdataYZbase

  Was this answer useful?  Yes

laxmikanta

  • Oct 21st, 2011
 

hi friend
All the above answer are true but one main difference between replace & translate is in case of replace 3rd argument is optional but in case of translate 3rd argument is most required or compulsory .. we cant live up it.. try it to find solution..

select replace('manager','man') rep from dual;........ok

select translate('manager','man') trns from dual;..........it will show error

  Was this answer useful?  Yes

vannalas

  • Nov 10th, 2011
 

Hi,

Here's one example of counting the no.of vowels in a string.

>select length('Sanjeeva kumar')-length(translate('Sanjeeva kumar','xaeiou','x')) from dual

  Was this answer useful?  Yes

#emadri

  • Jan 3rd, 2012
 

It is used to replace one string with another string, and
translate used to translate one character to another char.

  Was this answer useful?  Yes

  • Jan 8th, 2012
 

The Replace Function can replace the old substring whose length are not same to the length of new substring. (length of newsub DONT NEED eaqual tolength of oldsub )
But in Translate Function only can replace same length of the old substring.(Only can replate same number of characters of old sub)

example:
select replace(So What,o,ay) from dual --Say What
select replace (So What,So,Say) from dual --Say What
select translate(So What,So,Say) from dual --Sa What
select translate(So What,o,ay) from dual--Sa What

  Was this answer useful?  Yes

shaikh siraj

  • Apr 24th, 2016
 

String to string changing is called as replace.
Character to character changing is called as translate.

  Was this answer useful?  Yes

Manickavel

  • Dec 26th, 2016
 

Translate is used for character by character substitution where as Replace is used for substituting a character with a word

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions