Re: Numeric Function

From: Richard Kuhler <noone_at_nowhere.com>
Date: Thu, 08 Nov 2001 18:38:35 GMT
Message-ID: <LiAG7.23221$D5.8925666@typhoon.san.rr.com>

I interpreted the word 'numeric' to mean composed of all numeric characters rather than any valid number.

What about 7.3.2? Is that numeric? Does that pass your function?

What about '7 3 2'?  Is that numeric?  Does that pass your function?
What about '7.3E+00'?  Is that numeric?  Does that pass your function?
What about '-1'?  Is that numeric?  Does that pass your function?

(many more special cases) ...

If you want a function that tests whether a string is a valid number why not just...

function is_numeric (in_value in varchar2) return boolean is
begin
  if to_number(in_value) is null then
    return null;
  else
    return true;
  end if;

  exception
    when others then
      return false;
end;

Richard

MarkyG wrote:
>
> The suggestions so far are fine but...
>
> what about a number like 7.3?
>
> You may want to include a decimal point in the translate list.
>
> Try this. If you dont care about decimals, remove it from the translate list.
>
> FUNCTION Is_Numeric(in_value IN varchar2) RETURN boolean is
>
> v_retval boolean;
> BEGIN
>
> IF REPLACE(TRANSLATE(in_value,'0123456789.',' '),' ','') IS NULL THEN
> v_retval := TRUE;
> ELSE
> v_retval := FALSE;
> END IF;
>
> RETURN v_retval;
>
> END Is_Numeric;
>
> Mark
>
> Richard Kuhler <noone_at_nowhere.com> wrote in message news:<%yjG7.20674$D5.8286434_at_typhoon.san.rr.com>...
> > Connor McDonald wrote:
> > >
> > > NeedaHoliday wrote:
> > > >
> > > > Is there a function or method to search a string character and find
> > > > only numeric values?
> > > >
> > > > Thanks
> > >
> > > where
> > > replace(
> > > translate(col,'0123456789','9999999999')
> > > '9',null) is null
> > >
> > > or thereabouts
> > >
> >
> > or more efficiently....
> >
> > where translate(col, ' 0123456789', ' ') is null
> >
> >
> > Richard
Received on Thu Nov 08 2001 - 12:38:35 CST