Re: Oracle For Fun

Hello Jeff,

"Jeff" <jeff_at_work.com> wrote in message
news:bvlojh$mti$1_at_cronkite.cc.uga.edu...
> 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...

Care to elaborate on "doesn' handle null values ? Just being curious...

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> create or replace function f1( p_number in varchar2 ) return boolean is   2 begin
  3 return translate(p_number,'0123456789','9999999999') = '999-999-9999';
  4 end;
  5 /

Function created.

SQL> create table t1(x varchar2(20))
  2 /

Table created.

SQL> insert into t1 values('123-456-7890')   2 /

1 row created.

SQL> insert into t1 values('123-456-78j0')   2 /

1 row created.

SQL> insert into t1 values(null)
  2 /

1 row created.

SQL> set serverout on
SQL> declare
  2 l_str varchar2(20);
  3 begin
  4 for l in (select x from t1) loop
  5 if f1(l.x) then l_str := 'valid'; else l_str := 'not valid'; end if;
  6 dbms_output.put_line('<'||l.x||'>'||l_str);   7 end loop;
  8 end;
  9
 10 /

<123-456-7890>valid
<123-456-78j0>not valid
<>not valid  <-- null is not valid

PL/SQL procedure successfully completed.

SQL> ????????????

VC Received on Mon Feb 02 2004 - 19:14:32 CST