Re: Oracle For Fun

From: VC <boston103_at_hotmail.com>
Date: Thu, 29 Jan 2004 03:17:41 GMT
Message-ID: <pX_Rb.176163$xy6.832255@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:17:41 CST