I believe with a bit of lateral thinking this will do it for you, provided
that the pl/sql toolkit is loaded. You could (if you were better with
regular expressions than I) extend this so that you could have optional
area codes etc.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;
/
--
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
******************************************
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1075354128.934744_at_yasure...
> VC wrote:
>
> > Actually, the solution can be simplified:
> >
> > select case when
> > length(x) = 12 and
> > length(translate(x, '-0123456789', '*')) = 2 and
> > instr(x, '-') = 4 and
> > instr(x, '-', -1) = 8
> > then 'Valid'
> > else 'Invalid' end
> > from t1
> >
> >
> > VC
> >
> > "VC" <boston103_at_hotmail.com> wrote in message
> > news:pX_Rb.176163$xy6.832255_at_attbi_s02...
> >
> >>Hello Daniel,
> >>
> >>----- Original Message -----
> >>From: "Daniel Morgan" <damorgan_at_x.washington.edu>
> >>Newsgroups: comp.databases.oracle.server
> >>Sent: Wednesday, January 28, 2004 8:14 PM
> >>Subject: Re: Oracle For Fun
> >>
> >>
> >>
> >>>Write a simple function that will return a Boolean (True/False) as to
> >>>whether something has the format of a valid telephone number with area
> >>>code. It must check for the following:
> >>>
> >>>Three integers between 0 and 9
> >>>Followed by a dash '-'
> >>>Followed by three integers between 0 and 9
> >>>Followed by a dash '-'
> >>>Followed by four integers
> >>>The length must be 12.
> >>>
> >>>Hint: When I run the following query:
> >>>select * from user_source where name = <function_name>;
> >>>it returns only 7 lines of code.
> >>
> >>
> >>Given:
> >>
> >>create table t1(x varchar2(20));
> >>
> >>insert into t1 values('123-456-7893');
> >>insert into t1 values('12-3456-7893');
> >>insert into t1 values('-12345-67893');
> >>insert into t1 values('123-456-7893');
> >>insert into t1 values('1a3-456-7893');
> >>insert into t1 values('123-456-78s3');
> >>
> >>
> >>This should do it:
> >>
> >>select case when
> >> length(translate(x, '0123456789-', '9999999999')) = 10 and
> >> length(translate(x, '-0123456789', '*')) = 2 and
> >> instr(x, '-') = 4 and
> >> instr(x, '-', -1) = 8
> >> then 'Valid'
> >> else 'Invalid' end
> >>from t1
> >>
> >>Valid
> >>Invalid
> >>Invalid
> >>Valid
> >>Invalid
> >>Invalid
> >>
> >>
> >>Rgds.
> >>
> >>VC
>
> Good try but far to complex. And I said a function that returns a
> Boolean which means, at a minimum, you need this construct:
>
> CREATE OR REPLACE FUNCTION <function_name> (string_in VARCHAR2)
> RETURN BOOLEAN IS
>
> BEGIN
> <some_condition> RETURN TRUE
> <come_condition> RETURN FALSE
> END;
> /
>
> Try again. And then run the SQL statement I provided:
> select * from user_source where name = <function_name>;
> to look at what is in user_source.
>
> --
> 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 Thu Jan 29 2004 - 05:57:33 CST
