Re: replace or translate?

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Fri, 11 Feb 2005 10:02:52 -0500
Message-ID: <f09dd62805021107027985d5e1@mail.gmail.com>

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_at_naturaljoin.nl> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> 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

Received on Fri Feb 11 2005 - 10:05:39 CST