Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 30 Jan 2004 08:03:27 -0800
Message-ID: <1075478547.78774@yasure>

Niall Litchfield wrote:

> "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
> news:1a75df45.0401292119.79108a84_at_posting.google.com...
>

>>"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote
>>
>>
>>>I'd format this better but couldn't resist a solution with
>>>only 5 lines in user source...
>>>
>>>create or replace function is_phone(phone# in varchar2) return boolean
>>>is
>>>begin
>>>return owa_pattern.MATCH(phone#,'^[0-9]{3}-[0-9]{3}-[0-9]{4}$');
>>>END;
>>>/
>>>
>>
>>Dammit Niall. You could have won by putting the IS on the first line
>>and making it a 4 line solution.
>>
>>;-)

>
>
> Indeed I could have put it all on one line and made it a 1 line solution :(
>
> Somthing more suitable for production might read
>
> create or replace function is_phone(phone# in varchar2) return boolean
> is
> /*
> This function tests whether a given string
> represents a valid US phone number.
>
> requires OWA_TEXT and OWA_PATTERN packages preloaded
> these are part of the PL/SQL WebToolkit
> and can be installed by running owainst.sql from your rdbms/admin directory
>
> */
> regexp varchar2(255);
> retval boolean;
> begin
> retval := false;
> regexp := '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';
> /*
> Valid format is 999-999-9999 only.
> Modify the regular expression if you wish
> to match other formats, exclude the area code etc
> */
> retval := owa_pattern.MATCH(phone#,regexp);
> return retval;
> EXCEPTION
> when others then
> raise;
> END;
> /
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************

What kind of monster actually documents code. ;-)

-- 
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 Fri Jan 30 2004 - 10:03:27 CST