gomathi.e

• Mar 24th, 2006

 

replace chages the record in database.

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

meetnaveen4u

 Profile Answers by meetnaveen4u 

• Mar 24th, 2006

 

Replace used for String and Translate used for Character

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

 Profile Answers by 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

 Profile Answers by 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

 Profile Answers by 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

 Profile Answers by 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

 Profile Answers by 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 ...

ravindra1001

 Profile Answers by ravindra1001 

• Apr 7th, 2009

 

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

sankar babu

 Profile Answers by sankar babu 

• May 25th, 2011

 

replace function replaces entire sting as required but translate function translate character by character

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

vannalas

 Profile Answers by 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

#emadri

• Jan 3rd, 2012

 

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

• 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

shaikh siraj

• Apr 24th, 2016

 

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

Manickavel

• Dec 26th, 2016

 

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