Re: character data used as a number

In article <39d621f0.54898851_at_news.mindspring.com>,   willjamu_at_mindspring.com wrote:
> 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?
>
>

James, I am not at work so I can not double check my thoughts at the sql prompt but I believe you can use the replace function to convert the dash to a null and Oracle will automatically concatenate the remaining characters in the column together. You should then be able to convert the numeric characters to a number successfully.

You can find the function in the SQL manual. I think the code would look like:

select to_number(replace(the_col,'-','')) from dual
where the_col in ('GoodValue','BadValue'); to test this.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.