Re: character data used as a number

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;

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 - 10:38:46 CDT