Re: character data used as a number

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 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