Re: ** numeric check

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@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: