Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!tdsnet-transit!newspeer.tds.net!sn-xit-02!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: Daniel Morgan Newsgroups: comp.databases.oracle.server Subject: Re: Oracle For Fun Date: Mon, 02 Feb 2004 18:50:54 -0800 Organization: ATS Message-ID: <1075776599.3474@yasure> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 References: <1075188942.399322@yasure> <1075338777.438703@yasure> <1075354128.934744@yasure> <1075392160.811682@yasure> <1075478670.131740@yasure> <1075682270.753428@yasure> In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@oracle.advtechserv.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 112 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:253515 VC wrote: > Hello Jeff, > > "Jeff" wrote in message > news:bvlojh$mti$1@cronkite.cc.uga.edu... > >>In article <1075682270.753428@yasure>, Daniel Morgan > > 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 What, exactly, do you expect "insert into t1 values(null)" to do? Try select count(*) from t1; You need to create another column ... for example create table t1(seqno int, x varchar2(20)); insert into t1 values (1, '123-456-7890'); insert into t1 values (2, '123-456-78j0'); insert into t1 values (3, NULL); -- 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@x.washington.edu (replace 'x' with a 'u' to reply)