Re: Oracle For Fun

"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
> --
> 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:47:27 CST