Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 02 Feb 2004 16:14:20 -0800
Message-ID: <1075767204.885990@yasure>

Jeff wrote:

> In article <1075682270.753428_at_yasure>, Daniel Morgan <damorgan_at_x.washington.edu> wrote:
>
>

>>The contestants and results can be found at:
>>http://www.psoug.org/sql_fun.html
>>
>>One lesson comes through very clearly from the results ... test, test, 
>>test. Don't be too quick to think you know what's best just by looking
>>at the code you've written.

>
>
> Now, here I thought the object was to be as concise as possible.
>
> Of the four solutions, I think translate3 comes closest to winning the "most
> elegant" award. Translate2 should be disqualified because it doesn't handle
> null values... but it does offer a refinement to translate3:
>
> create or replace function translate3(p_code varchar2)
> return boolean is
>
> begin
> return translate(nvl(p_code,'x'),'0123456789','9999999999')='999-999-9999';
> end translate3;

One could also handle NULLS with:

CREATE OR REPLACE FUNCTION translate3(p_code VARCHAR2 DEFAULT 'x') RETURN BOOLEAN IS BEGIN
   RETURN translate(p_code,'0123456789','9999999999')='999-999-9999'; END translate3;
/

Which is cleaner still.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Feb 02 2004 - 18:14:20 CST