SQL - Replace and Translate

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 161 of 171

Replace and Translate

How and in what way REPLACE is different from TRANSLATE?

Total Answers and Comments: 7

Last Update: October 28, 2009 Asked by: ravi.allam9

Sponsored Links

Best Rated Answer

—

Submitted by: seenu333p

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

Above answer was rated as good by the following members:
dhivya1729, amitbehera

June 10, 2008 00:16:09

ashish88

Member Since: June 2008 Contribution: 8

RE: Replace and Translate

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;

Is this answer useful? Yes | No

July 13, 2008 04:24:41

suresh.kurapati11

Member Since: July 2008 Contribution: 2

RE: Replace and Translate

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;

Is this answer useful? Yes | No

September 09, 2008 05:34:30

seenu333p

Member Since: July 2008 Contribution: 2

RE: Replace and Translate

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

Is this answer useful? Yes | No

1 0

Overall Rating: +1

October 25, 2008 03:08:33

amitsrivastava115

Member Since: October 2007 Contribution: 3

RE: Replace and Translate

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

Is this answer useful? Yes | No

March 05, 2009 02:09:29

sharmasl

Member Since: March 2009 Contribution: 10

RE: Replace and Translate

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

Is this answer useful? Yes | No

1 0

Overall Rating: +1

July 28, 2009 07:22:37

Rohit_Ash

Member Since: May 2009 Contribution: 6

RE: Replace and Translate

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

Is this answer useful? Yes | No

October 28, 2009 02:22:05

Rabindra_Kumar

Member Since: October 2009 Contribution: 6

RE: Replace and Translate

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

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank