Re: Oracle For Fun

"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
******************************************