Re: ** numeric check

An oldy, but a goody.
BTW, an internet search would turn up several references to this.

Essentially you need to use the to_number() function, and test for errors.

There are other ways, but this has proven to be the fastest.

I'm sure that someone will point out that the 'raise' is unnecessary.  :)

Jared

create or replace function is_number( chk_data_in varchar2 )
return boolean
is
        dummy number(38,4);
begin
        dummy := to_number(chk_data_in);
        return true;
exception
when value_error then
        return false;
when others then
        raise;
end;
/

show errors function is_number


declare
        v_test varchar2(10) := '1E';
begin
        if is_number(v_test) then
                dbms_output.put_line(v_test || ' is a number');
        else
                dbms_output.put_line(v_test || ' is NOT a number');
        end if;
end;
/




A Joshi <ajoshi977@xxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
07/12/2004 02:29 PM
Please respond to
oracle-l@xxxxxxxxxxxxx


To
oracle-l@xxxxxxxxxxxxx
cc

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: