Re: SQL Function

From: Ken Denny <ken_at_kendenny.com>
Date: Tue, 30 Apr 2002 21:35:38 GMT
Message-ID: <Xns9200B4E73F4kendenny@65.82.44.7>

"Prasad Yarlagadda" <prasad.yarlagadda_at_motorola.com> wrote in news:aajlft$b8u$1_at_localhost.localdomain:

> Is there any standard SQL function available to check whether an array
> of characters is a String or a Number? I am looking for something like
> IS_NUMBER or IS_CHARACTER. I don't want to write a function for it if
> oracle has already provided.
>
> TIA,
> Prasad
>
>

Lots of replies but none with the solution to your problem, so try this:

   FUNCTION is_number (numstring VARCHAR2) RETURN BOOLEAN    IS
/*
|| Returns true if the string contains a valid number */

      w_num NUMBER;
   BEGIN

      w_num := TO_NUMBER(numstring);
      RETURN TRUE;
   EXCEPTION
      WHEN INVALID_NUMBER OR VALUE_ERROR THEN
         RETURN FALSE;

   END is_number;

-- 
Ken Denny (PL/SQL guru in search of employment)
http://www.kendenny.com/

Planting vegetables is a great money-saver. Over the course of a summer, 
you could shave $75 off your grocery bill with just a few hundred hours of 
work. 

Received on Tue Apr 30 2002 - 16:35:38 CDT