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

"Justin J. Risser" <JJRisser_at_lancasterlabs.com> writes:

> 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

Hi Justin,

you could write a test function in PL/SQL like this:

CREATE FUNCTION is_number

   (p_val IN VARCHAR2)
RETURN BOOLEAN
IS

   l_num NUMBER;
BEGIN    l_num := TO_NUMBER(p_val);

   RETURN TRUE;    EXCEPTION WHEN value_error THEN
   RETURN FALSE; END is_number;

HTH,
Peter

--
Peter Schneider
pschneider_at_knuut.de Received on Mon Aug 16 1999 - 16:17:00 CDT