ashish88

 Profile Answers by ashish88 Questions by ashish88

• Jun 10th, 2008

 

Replace function repalce the value where ever they was found  string .
Select  Replace ('*THE*','*','TROUBLE') from dual;
Translate  Function  replace the value whereever they  have found one word of the string.
Select Translate('AtheN','AN','the') from dual;

suresh.kurapati11

 Profile Answers by suresh.kurapati11 

• Jul 13th, 2008

 

REPLACE: It is used to replaces the value,without giving the alternative letter or word.

Ex:1. select replace(ename,'S') from emp;
     2.select replace(ename,'S','A') from emp;

TRANSLATE: Here you must apply the with replace value,instead of giving value

Ex: select translate(ename,'S','A') from emp;

seenu333p

 Profile Answers by seenu333p 

• Sep 9th, 2008

 

Translate:

It is used to replace charecter by charecter

Ex:

Select translate('ashok','xp','sk')
from dual;
 
result:   ashok


Replace:

It is used to replace word by word

Ex:
select replace('tech world','Tech','technology')
from dual;

result: technology world

amitsrivastava115

 Profile Answers by amitsrivastava115 

• Oct 25th, 2008

 

Repalce and translate are both similar in appearance , but they produce quite different results, while replace as the name suggests replaces an entire string with another one, translate replaces one charecter by other mentioned in the list.

lets see an example, if we want to count the number of times vowels occurs in a string we use translate instead of replace as we know all the vowels i.e 'aeiou' will not appear together but at differnt locations.

lets see the exapmle

with data as (select'Whose line is it anyway' line FROM dual)
select length(line) - length(translate(line,'xaeiou','x')) from data;
here x is replaced by x as there is no translation available for aeiou they will be dropped and thus we can count how many times they appear in a string.

Now in case of replace suppose we want to find how many times a particular string appears with in a string.here we use replace as we know the string we need to search will appear as together.

suppose if i need to find how many times 'am' appears in amit i know one and once only, its not like i search for 'am' in 'aim' and can say once as here the 'am' does not appear even once.

lets see and example.

here i create a function to count the number of times a string appears with in a string.

create or replace function countamit(str varchar2,search varchar2 := null)
return number is
begin
if (serch is null) then
 return (length(str));
else
 return (length(str) - nvl(length(replace(str,search,'')),0))/
lengtth(search);
end if;
end;

here we make use of search instead of translate.

regards
amit

sharmasl

 Profile Answers by sharmasl 

• Mar 5th, 2009

 

Translate check and replace character by character in the string like this
example-

SELECT TRANSLATE('AGDFADCDA ANLKAKE ABZUYA','AG','ZY') FROM DUAL;

here in all three words ('AGDFADCDA ANLKAKE ABZUYA') the character ‘A’ , ‘G’
and ‘AG’ are replace by character ‘Z’,’Y’ or ‘ZY’ . So the output is: -
ZYDFZDCDZ ZNLKZKE ZBZUYZ But in other hand Replace check and replace word by
word in the string like This example-

SELECT REPLACE ('AGDFADCDA ANLKAKE ABZUYA','AG','ZY') FROM DUAL;
Here in all three words ('AGDFADCDA ANLKAKE ABZUYA') only the first word where
‘AG’ came together is changed and remain word are same. It is because in other
words ‘AG’ not came together and replace can change words. So the output is: -
ZYDFADCDA ANLKAKE ABZUYA

Read it careful and try to understand.

Thanks

Rohit_Ash

 Profile Answers by Rohit_Ash 

• Jul 28th, 2009

 

replace( STRING, string_to_replace, replacement_string) replaces string_to_replace  by  replacement_string as a whole.

All Bs would be replaced by O.
 
SQL> select replace('ABC PBR XBZ','B','O') from dual;
REPLACE('AB
-----------
AOC POR XOZ

There wont be any change since APX string is not there.

SQL> select replace('ABC PBR XBZ','APX','MNO') from dual;
REPLACE('AB
-----------
ABC PBR XBZ

Translate(STRING, string_to_replace, replacement_string) replaces the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

All Bs would be replaced by Os.

SQL> select translate('ABC PBR XBZ','B','O') from dual;
TRANSLATE('
-----------
AOC POR XOZ

All As would be replaced by M, all Ps would be replaced by N and all Os would be replaced by Os.

SQL> select translate('ABC PBR XBZ','APX','MNO') from dual;
TRANSLATE('
-----------
MBC NBR OBZ

Rabindra_Kumar

 Profile Answers by Rabindra_Kumar 

• Oct 28th, 2009

 

Replace and Translate both works with three argument

replace(arg1,arg2,arg3)
translate(arg1,arg2,arg3)

the inbuilt function "Replace" replace the arg2 with arg3. it replaces the whole strig.
while the inbuilt function "Translate" replace the each caharacter of arg2 with the corresponding character of argument 3.

if write down the following query

SELECT Translate('Ramesh Kumar Jha','Ramesh','Mukesh') FROM dual;

Result would be

Mukesh Kukur Jhu

sandip88

 Profile Answers by sandip88 

• Feb 9th, 2010

 

Translate does character by character substitution,
where replace can substitute a singele character with a word

zahar

 Profile Answers by zahar 

• Jun 10th, 2011

 

REPLACE :
 replace function is:

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string

For example:
 

replace('123123tech', '123');	would return 'tech'
replace('123tech123', '123');	would return 'tech'
replace('222tech', '2', '3');	would return '333tech'

:: extact one to one matching of character string like '123' or 'abc'
   replacement character is not needed.




Translate :

translate( string1, string_to_replace, replacement_string )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

For example:

translate('1tech23', '123', '456');	would return '4tech56'
translate('222tech', '2ec', '3it');	would return '333tith'

:: extact one to on e matching of character like 'a' or '1'
   replace character is needed