Re: character data used as a number

"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
>
>
Received on Sat Oct 28 2000 - 11:07:29 CDT