Re: ** numeric check

From: <Jared.Still_at_radisys.com>
Date: Mon, 12 Jul 2004 17:28:19 -0700
Message-ID: <OF79C3E3A9.51A8B6C2-ON88256ED0.000267E7-88256ED0.00029538@radisys.com>

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_at_yahoo.com>
Sent by: oracle-l-bounce_at_freelists.org
07/12/2004 02:29 PM
Please respond to
oracle-l_at_freelists.org

To
oracle-l_at_freelists.org
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_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

Received on Mon Jul 12 2004 - 19:25:18 CDT