Re: Checking for Numeric Values

From: Songbird <GCHEUNG_at_oldec1.agw.bt.co.uk>
Date: 1997/03/21
Message-ID: <5gu3p2$fl7@pheidippides.axion.bt.co.uk>#1/1

In article <5gpoa4$d8n_at_news.acns.nwu.edu>, stevem_at_nwu.edu says...
>
>
>I am working with data that sometimes is entirely numeric, sometimes
>alphanumeric (300,350A,400F, for example).
>
>Is there a way to imitate the IsNumeric function in MS Basic, which returns a
>Yes/No depending on whether the value can be converted to a number?
>
>TO_NUMBER(350A) yields ORA-01722: invalid number, and the program stops.

Here are a couple of suggestions:

FUNCTION isnumber (data_in IN VARCHAR2) RETURN INTEGER IS
BEGIN
   RETURN TO_NUMBER(data_in);
EXCEPTION
   WHEN INVALID_NUMBER THEN
   RETURN -1;
END; '123A' returns -1, '1234' returns 1234, etc.

FUNCTION data_fmt (data_in IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
   RETURN TRANSLATE(UPPER(data_in),

          '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
          '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX');

END; '123A' returns '999X', '1234' return '9999'. etc.

Gary. Received on Fri Mar 21 1997 - 00:00:00 CST