Re: Oracle For Fun

From: Tony <andrewst_at_onetel.net.uk>
Date: 3 Feb 2004 02:34:23 -0800
Message-ID: <c0e3f26e.0402030234.2ba855b4@posting.google.com>

"VC" <boston103_at_hotmail.com> wrote in message news:<YBCTb.205971$I06.2284013_at_attbi_s01>...
> Hello Jeff,
>
> "Jeff" <jeff_at_work.com> wrote in message
> news:bvlojh$mti$1_at_cronkite.cc.uga.edu...
> > 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

Well, to be pedantic, it doesn't say that NULL is not valid, it says its validity is unknown (which is correct for a NULL):

  1 declare

  2      l_str varchar2(20);
  3      b boolean;
  4  begin
  5      for l in (select x from t1) loop
  6        b := f1(l.x);
  7        if b then l_str := 'valid';
  8        elsif not b then l_str := 'not valid';
  9        else l_str := 'unknown'; end if;
 10        dbms_output.put_line('<'||l.x||'>'||l_str);
 11      end loop;

 12* end;
tandrews_at_IDEV
SQL> /

<123-456-7890>valid
<123-456-78j0>not valid
<>unknown

PL/SQL procedure successfully completed.

My version (translate3) with the NVL makes NULL invalid. This could also be construed as correct, since in Oracle NULL and '' are indistinguishable, and '' is certainly not of the correct form.

(Brace self for backlash...) Received on Tue Feb 03 2004 - 04:34:23 CST