Re: replace or translate?
- From: Sami Seerangan <dba.orcl@xxxxxxxxx>
- To: Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx>
- Date: Fri, 11 Feb 2005 10:02:52 -0500
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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
