Re: Checking for Numeric Values

From: Gert Rijs <gem_at_wirehub.nl>
Date: 1997/03/20
Message-ID: <01bc34f1$f00d56e0$0100007f@gertrijs>#1/1

Steven Moyano <stevem_at_nwu.edu> wrote in article <5gpoa4$d8n_at_news.acns.nwu.edu>...
>
> I am working with data that sometimes is entirely numeric, sometimes
> alphanumeric (300,350A,400F, for example).
>
> Is there a way to imitate the IsNumeric function in MS Basic, which
 returns a
> Yes/No depending on whether the value can be converted to a number?
>
> TO_NUMBER(350A) yields ORA-01722: invalid number, and the program stops.
>
> Thanks very much for your time and help.
in a pl/sql block (or procedure):
declare

   thenum number;
begin

   ...
   begin
     thenum := to_number('350A');
   exception when others then

      if sqlcode=-1722 then
         thenum:=0;
      else
         raise; // some other error
      end if;

   end;
   ... use thenum
end;

        Gert Received on Thu Mar 20 1997 - 00:00:00 CST