Re: ** numeric check
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Jul 2004 00:43:47 +0300
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 //www.freelists.org/archives/oracle-l/
> FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
