Re: replace or translate?

Lex,

Thanks for your response. Here the result is opposite to what you mentioned.

As shown below, I wanted to replace 2 single characters in
URL_ADDERSS_TEXT sting in SELECT clause.
But replace defeats translate fuction even though I am using it here
to replace single character.

Sample url_addr_text:
=====================
/code/tools/site/Renderer.jhtml?cp=/public/groupsite/employment/graduates/en/grad_default.html&bu=groupsite&toolName=graduates&ln=en&isPc=true&pId=d71414c0-c262

using replace
==============
substr(replace(replace(a.url_addr_text,';','/'),'?','/'),1,instr(replace(replace(a.url_addr_text,';',
'/'),'?','/'),'/',1,2))

using translate
===============

substr(translate(a.url_addr_text,';?','//'),1,instr(translate(a.url_addr_text,';?','//'),'/',1,2))



On Fri, 11 Feb 2005 15:50:05 +0100, Lex de Haan
<lex.de.haan@xxxxxxxxxxxxxx> wrote:
> the two functions in this thread have different purposes;
> TRANSLATE replaces individual characters, and REPLACE is
> meant to search and replace words. An example from my book:
> 
> SQL> select translate('beer bucket','beer','milk') as translate
>  2  ,      replace  ('beer bucket','beer','milk') as replace_1
>  3  ,      replace  ('beer bucket','beer')        as replace_2
>  4  from   dual;
> 
> TRANSLATE   REPLACE_1   REPLACE
> ----------- ----------- -------
> miik muckit milk bucket  bucket
> 
> I think it is "safe" to assume that the two functions are optimized for
> their purpose ...
> and if expression evaluation is truly causing unacceptable performance
> problems,
> a function-based index might be the solution.

> Lex.
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Sami Seerangan
> Sent: Friday, February 11, 2005 15:25
> To: Bill Coulam
> 
> I ran a test case on 10M rows (do have complex strings) with two nested
> REPLACE calls vs. a simple TRANSLATE. The result is opposite to yours.
> 
> Replace took 13 mins
> Translate took 20 mins
>
--
http://www.freelists.org/webpage/oracle-l

Other related posts: