Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: To_Number
When somebody complains that, when they do an "order by" and get that 100 is less than 2, now you know what to tell them .... "Because". (As you might guess, you're not the first to see this.)
-----Original Message-----
Sent: Thursday, December 05, 2002 7:29 PM
To: Multiple recipients of list ORACLE-L
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-----
[mailto:JApplewhite_at_austin.isd.tenet.edu]
Sent: Thursday, December 05, 2002 5:34 PM
To: Multiple recipients of list ORACLE-L
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)
JApplewhite_at_austin.isd.tenet.edu
Rachel Carmichael To: Multiple recipients of list ORACLE-L <wisernet100_at_Y <ORACLE-L_at_fatcity.com> AHOO.COM> cc: Sent by: Subject: Re: To_Number root_at_fatcity.c 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?
--- JApplewhite_at_austin.isd.tenet.edu 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: JApplewhite_at_austin.isd.tenet.edu 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: ListGuru_at_fatcity.com (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: slee_at_dollar.com 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: ListGuru_at_fatcity.com (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).Received on Fri Dec 06 2002 - 07:20:11 CST
![]() |
![]() |