RE: To_Number

[Toepke, Kevin M]

Title: To_Number

My guess is that you have leading or trailing spaces. try

select to_number(LTRIM(RTRIM(unit_cost)),'$999,999,999.99') from elas.qdr

-----Original Message-----
From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 05, 2002 2:24 PM
To: Multiple recipients of list ORACLE-L
Subject: To_Number

I have a table which contains a Unit_Cost varchar2(16) which contains $34,000.05.  I can enter select to_number('$34,990.08','$999,999,999.99') from dual; and the results is 34990.08.  However when I enter select to_number(unit_cost,'$999,999,999.99') from elas.qdr I get ora-01722: invalid number. 

Is there any other way to do this?  I am trying to add a varchar2 field that contains $ and commas.  I thought the to_number function would convert the data to a number field.

Thanks,

Laura