Re: character data used as a number

From: spencer <spencerp_at_swbell.net>
Date: Sat, 28 Oct 2000 19:39:18 -0500
Message-ID: <AoKK5.238$n34.112309@nnrp1.sbc.net>

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:39FACFB6.2D60_at_yahoo.com...
> spencer wrote:
> >
> > "Mark Bole" <makbo_at_ecis.com> wrote in message news:39FAF306.82A2CCFC_at_ecis.com...
> > > CREATE or REPLACE function is_number (x in varchar2)
> > > return number
> > > is
> > > dummy number;
> > > begin
> > > select to_number(x) into dummy from dual;
> > > return 1;
> > > exception
> > > when invalid_number
> > > then
> > > return 0;
> > > end;
> >
> > or you could try something like this to catch the exception
> > (numeric conversion error), and return a NULL instead.
> >
> > FUNCTION number_or_null
> > (x IN VARCHAR2)
> > IS
> > BEGIN
> > RETURN TO_NUMBER(x);
> > EXCEPTION
> > WHEN OTHERS THEN
> > RETURN NULL;
> > END number_or_null;
> >
> > >
> > > Steve Haynes wrote:
> > >
> > > > In article <39d621f0.54898851_at_news.mindspring.com>, James A. Williams
> > > > <willjamu_at_mindspring.com> writes
> > > > >I have a user with an application with a column of a table defined as
> > > > >VARCHAR2 instead of NUMBER for whatever reason. The table has no
> > > > >constraints about the column in question at the moment. When the data
> > > > >comes from DB2 on OS/390 (where there can be a dash in the number
> > > > >occasionally it is inserted into the Oracle table. Users use the TONUM
> > > > >function to convert the value to a number for processing. We if dashes
> > > > >are in the column this does not work. What would be a query I could
> > > > >use to check for all varchar2 's that can't be converted to a number?
> > > > >
> > > > just use to_number and catch the exceptions...
> > > > Steve
> > >
> > >

>

> where
> replace(
> translate(col,'0123456789','999999999'),
> '9',null) is null

>

> HTH

>

Connor, this will indeed check that all characters in the string are characters 0 thru 9. but this is more restrictive than the TO_NUMBER function. for example, the character string may include a leading sign and a decimal separator character. i still believe a better way to check whether the TO_NUMBER function can convert a character string into a number is to use the TO_NUMBER function, and catch the exception. if there are other restictions on the numeric value, then include a format string as a second argument to the TO_NUMBER function, or include the check(s) and/or additional conversions in a user written function. for example:

FUNCTION positive_integer_or_null
(xs IN VARCHAR2)
IS
  xn NUMBER;
BEGIN

• convert to number, and catch exception BEGIN xn := TO_NUMBER(xs); EXCEPTION WHEN OTHERS THEN RETURN NULL; END;
• check that value is an integer IF NOT (xn = TRUNC(xn)) THEN RETURN NULL; END IF;
• check that value is positive or zero IF xn < 0 THEN RETURN NULL; END IF; RETURN xn; EXCEPTION WHEN OTHERS THEN RETURN NULL; END;

> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)

>

> "Early to bed and early to rise,
> makes a man healthy, wealthy and wise." - some dead guy
Received on Sat Oct 28 2000 - 19:39:18 CDT