Re: Oracle For Fun

From: VC <boston103_at_hotmail.com>
Date: Thu, 29 Jan 2004 03:30:58 GMT
Message-ID: <S7%Rb.172995$I06.1737861@attbi_s01>

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
>
>
Received on Wed Jan 28 2004 - 21:30:58 CST