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).