Since we don't have that many 3rd party software packages I did make the mistake of asking 'Why??' and received 'Because!' so I too am trying to jump in and 'fix it'. As I responded to another email earlier, the RTrim worked because there were spaces after the amount which was causing the problem. The only problem now is I have one record (so far) that has a unit cost that looks like any other unit cost, yet I receive 'invalid number' for it. The only thing I can figure is that there must be an unprintable character in the field that I cannot see and rtrim is not deleting since it isn't a space.
Laura
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 05, 2002 5:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: To_Number
Rachel,
We have two Third Party apps here for Finance and Student Information that
do ridiculous stuff like this so often, I just immediately jumped into "fix
it" mode without even questioning. "Don't even ask why" is our motto. ;
-)
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Rachel
Carmichael To: Multiple recipients of list ORACLE-L
<wisernet100@Y <[EMAIL PROTECTED]>
AHOO.COM> cc:
Sent by: Subject: Re: To_Number
[EMAIL PROTECTED]
om
12/05/2002
03:49 PM
Please respond
to ORACLE-L
Am I the only one wondering why an obviously numeric field
(unit_cost???) is being stored as varchar?
--- [EMAIL PROTECTED] wrote:
>
> Laura,
>
> Are those really zeros in $34,000.05 or are they letter Os? If so
> use
> Replace. (Beware of letter l being used instead of numeral 1 as
> well.)
>
> Any leading or trailing spaces? If so use Trim(unit_cost).
>
> Just a couple of quick suggestions.
>
> Jack C. Applewhite
>
>
>Burton, Laura
>
> 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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).