-----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_NumberLaura are you sure you aren't trying this with the quotes? See below
SQL> select * from testnum;
COL1
---------------
$24,990.09SQL> 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 numberSQL> select to_number(col1,'$999,999,999.99') from testnum;
TO_NUMBER(COL1,'$999,999,999.99')
---------------------------------
24990.09Lisa 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_NumberI 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
Title: 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.
- To_Number Burton, Laura L.
- RE: To_Number Koivu, Lisa
- RE: To_Number Kevin Lange
- RE: To_Number Kevin Lange
- RE: To_Number Mercadante, Thomas F
- RE: To_Number Toepke, Kevin M
- RE: To_Number Koivu, Lisa
- Re: To_Number JApplewhite
- Re: To_Number Rachel Carmichael
- RE: To_Number Burton, Laura L.
- RE: To_Number Fink, Dan
- Re: To_Number JApplewhite
- RE: To_Number Burton, Laura L.
- RE: To_Number Rachel Carmichael