Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 29 Jan 2004 08:03:39 -0800
Message-ID: <1075392160.811682@yasure>

VC wrote:

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

>
>
> Agreed. It can even sipmpler:
>
> select decode(translate(x, '0123456789', '9999999999'), '999-999-9999'),
> 'Valid', 'Invalid') from t1;
>
> I am no sure why you want a function because a single SQL statement is
> always faster -- but here's one:
>
> create or replace function f1( p_number in varchar2 ) return boolean is
> begin
> return translate(p_number,'0123456789','999999999') = '999-999-9999';
> end;
>
>
> Rgds.
>
> VC

No particular reason. It is just how I originally wrote the problem.

The point of putting these out here is not that there is a single correct answer ... but rather to give anyone interested a chance to challenge their own abilities and for beginners to see not only new possible solutions ... but that there are many possible solutions.

-- 
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 - 10:03:39 CST