Re: ** numeric check

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Tue, 13 Jul 2004 00:43:47 +0300
Message-ID: <116c01c46859$55fb7f20$bff923d5@porgand>

Hi!

You could try to use TO_NUMBER function on the string and catch the possible exception, returning an error code if unsuccessful. An example I've found from internet:

FUNCTION is_numeric( p_text IN VARCHAR2) RETURN INTEGER
IS
  n NUMBER;
BEGIN
  IF INSTR(UPPER(p_text),'E') > 0 THEN
    RETURN 0;
  END IF;
  n := TO_NUMBER(p_text);
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

The check for 'E' letter is because in Oracle E may mean exponent value (it should be possible to disable this behaviour using additional number format string in TO_NUMBER function..

Tanel.

Original Message ----- From: "A Joshi" <ajoshi977_at_yahoo.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, July 13, 2004 12:29 AM Subject: ** numeric check

> Hi,
> I have a varchar2 column which contains some text and some totally
numeric values. Is there an easy way to check if the value is totally numeric instead of looking at ascii values etc. Thanks for your help.
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Mail - 50x more storage than other providers!
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jul 12 2004 - 16:40:50 CDT