Very Possible. I'm 8.1.7.2 W2K sp2
<whack>
-----Original Message-----
From: Kevin Lange [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, December 05, 2002 3:30 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: To_Number
Lisa;
I wonder if it depends on the DB version. I did this on an 8.0.5 and got the invalid number error running the exact query that succeeded on yours. I had to do a replace on both the commas and the dollar sign.
-----Original Message-----
From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 05, 2002 1:55 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: To_Number
Laura are you sure you aren't trying this with the quotes? See below
SQL> select * from testnum;
COL1
---------------
$24,990.09
SQL> select to_number('col1','$999,999,999.99') from testnum;
select to_number('col1','$999,999,999.99') from testnum
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select to_number(col1,'$999,999,999.99') from testnum;
TO_NUMBER(COL1,'$999,999,999.99')
---------------------------------
24990.09
Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
-----Original Message-----
From: Burton, Laura L. [SMTP:[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
