Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 28 Jan 2004 21:29:51 -0800
Message-ID: <1075354128.934744@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 Wed Jan 28 2004 - 23:29:51 CST