Re: Is_number () like function

From: John Thwaites <John.Thwaites_at_alliedsignal.com>
Date: Wed, 22 Jul 1998 16:24:03 -0400
Message-ID: <35B64A63.72A538F9@alliedsignal.com>

You can do this with the help of a user function. You need to create a package containing the function with an associated pragma defined. The function attempts to convert the string to a number and returns a 'Y' if successful or an 'N' if the exception is triggered.

Package Spec:

Package MY_UTIL
  IS

    Function Is_Numeric

     ( a_Number_String IN varchar2 )
        Return  varchar2;

    PRAGMA Restrict_References( Is_Numeric, WNDS, WNPS, RNDS, RNPS );

END; Package Body:

Package Body MY_UTIL
IS

   FUNCTION Is_Numeric

     ( a_Number_String IN varchar2)
     RETURN  varchar2

    IS
    v_number number;
   BEGIN

        v_number := to_number( a_Number_String );
        Return 'Y';
   EXCEPTION
      WHEN Others THEN
          Return 'N' ;

   END; END; Then you can use the function in SQL statements:

Select col1, col2
from some_table
where MY_UTIL.IS_NUMERIC( col1 ) = 'Y'

John Thwaites
AlliedSignal, Inc.
Polymers Division

Christophe Renard wrote:

> Hi,
>
> I was wondering (and did not found in the documentation), if PL/SQL has a
> function that would work like that:
> select * from myTable where is_number(myfield)
> that would give me all the rows where myfield is a varchar2 describing a
> valid number.
>
> The only way I found was to try to catch the Not A Number exception when
> converting with the to_number() function, nut i believe there is a more
> elegant (and efficient) way to deal with it.
>
> Any help will be welcome
>
> Thanx
>
> Christophe Renard
>
Received on Wed Jul 22 1998 - 15:24:03 CDT