Re: Checking for Numeric Values

From: Brian Membrey <horsemth_at_mel.switch.net.au>
Date: 1997/03/20
Message-ID: <01bc34f3$6a5cdfa0$0ca320cb@default>#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).
>

Try the following :

create or replace function IsNumeric (charvar VARCHAR2)

     return BOOLEAN is
numvar      NUMBER;

not_numeric EXCEPTION;
PRAGMA EXCEPTION_INIT (not_numeric, -06502);

begin
numvar := charvar;
return TRUE;
exception
when not_numeric then
  return FALSE;
end;

This will create a PL/SQL function which handles the possibility of a non-numeric error and returns TRUE or FALSE . Note that the error code returned by Oracle for non-numerics varies depending on the actual product being used - hence the PL/SQL function checks for -06502 rather than -1722  To use the function in SQL, something like .....

set serveroutput On
begin
if IsNumeric('A25') then

   dbms_output.put_line('Numeric! Beauty!'); else

   dbms_output.put_line('No! Bummer!');
end if;
end;
/
No! Bummer! Received on Thu Mar 20 1997 - 00:00:00 CST