Re: Checking for numerics

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Sun, 05 Mar 2000 14:48:42 GMT
Message-ID: <89ts4a$1ot$1@nnrp1.deja.com>

In article <Mwnw4.230$BZ3.10971_at_news.corecomm.net>,   "The Hudsons" <goosedog_at_corecomm.net> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_013E_01BF8645.63A3AE40
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
>
> I have a PL/SQL program that needs to read a varchar field (which is =
> supposed to contain a dollar amount), do a to_number on the value,
and =
> then do several calculations with the field. My problem is I've =
> discovered that there are some non-numeric characters in my column. =
> There are some records that are completely alpha, and there are some
not =
> quite as offensive records that have the numbers prefixed w/ a dollar
=
> sign ($). My question is, how can I check (prior to doing my
to_number) =
> whether or not the field contains any non-numeric characters. Thanks
in =
> advance.
>
> Kelly

write a small function to do the 2 number. have this function catch the value_error exception and return NULL or some value meaningful to you. for example:

ops$tkyte_at_8i> create or replace function my2number( p_str in varchar2 ) return number
  2 is
  3 begin
  4 return to_number( p_str );   5 exception
  6 when value_error then return null;   7 end;
  8 /

Function created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> exec dbms_output.put_line( my2number( 'a' ) );

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> exec dbms_output.put_line( my2number( '1' ) ); 1

PL/SQL procedure successfully completed.

Now the calling code can check to see if the value is OK by looking at the return value from this function. If NULL, its not a number.

--

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Mar 05 2000 - 08:48:42 CST