| |
GeekInterview.com > Interview Questions > Oracle > SQL
| Print | |
Question: Replace and Translate
Answer: How and in what way REPLACE is different from TRANSLATE? |
| October 10, 2008 03:08:33 |
#4 |
| amitsrivastava115 |
Member Since: October 2007 Total Comments: 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 |
| |
Back To Question | |
