Re: Please Help: Check to see if a string is a valid number in procedure/function.

Try this not-so-elegant but working function:

  1 create or replace function is_num
  2 ( p_num varchar2 )
  3 return number
  4 is
  5 v_num number;
  6 begin
  7 v_num := to_number(p_num);
  8 return 1;
  9 exception
 10 when value_error
 11 then
 12 return 0;
 13* end;
SQL> /
Function created.

SQL> select is_num('a3') from dual
  2 /

IS_NUM('A3')

           0

SQL> select is_num('3') from dual
  2 /

IS_NUM('3')

          1

Jean-Luc

Justin J. Risser wrote in message
<10907816B00AD211A93300A0C9B3BBE6021E5989_at_xdelta.lancasterlabs.com>...
>Is there a way to gracefully check to see if a string can be converted
>to a number in a procedure or function? I have tried using the
>TO_NUMBER(), but that just dies when I send it a string with letters in
>it. Is there a way to trap that error, or another way to check to see
>if a string is a valid number? Thank you.
>
>Justin
>
Received on Fri Aug 20 1999 - 08:26:39 CDT