Subject: RE: To_Number
m
Sent by: root
12/06/2002
09:44 AM
Please
respond to
ORACLE-L
Thanks for the email!! I did
No, she isn't.
On Thursday 05 December 2002 14:19, Fink, Dan wrote:
Yes, yes you are.
-Original Message-
Sent: Thursday, December 05, 2002 2:50 PM
To: Multiple recipients of list ORACLE-L
Am I the only one wondering why an obviously numeric field
(unit_cost???) is being stored
Look at NLS_NUMERIC_CHARACTERS ,.
-Message d'origine-
De: Jared Still [mailto:[EMAIL PROTECTED]]
Date: vendredi 6 décembre 2002 09:59
À: Multiple recipients of list ORACLE-L
Objet: Re: To_Number
No, she isn't
Carmichael To: Multiple recipients of list
ORACLE-L
wisernet100@Y[EMAIL PROTECTED]
AHOO.COMcc:
Sent by: Subject: Re: To_Number
[EMAIL PROTECTED
I always ask the obvious questions... because the answer is usually
oops we screwed up and it fixes the problem
I STILL think because is an unacceptable answer to why did you make
a number field varchar?
--- Mercadante, Thomas F [EMAIL PROTECTED] wrote:
Oh sure. you had to ask the obvious
Title: RE: To_Number
Thanks for the email!! I did not know about Dump so I learned something new. The record I thought I had a problem with was ok and that is why I did not see anything amiss. However the record after this one was $20041-94. Corrected that and got $145.34 EA. I just told
Subject: RE: To_Number
m
Sent by: root
.To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
BurtonL cc:
@prismplus.coSubject: RE: To_Number
m
Sent by: root
12/06/2002
09:44 AM
recipients of
list ORACLE-L [EMAIL PROTECTED]
BurtonL cc:
@prismplus.coSubject: RE: To_Number
m
Sent by: root
12/06/2002
09:44 AM
Title: 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
Title: To_Number
I
believe its the $ and , in the data. I got the same error until I
did
select
to_number(replace(replace(unit_cost,'$',''),',','')) from
elas.qdr
-Original
Message-From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 05, 2002 1:24
PMTo:
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.
-Original Message-From: Koivu, Lisa
Title: To_Number
Laura,
Works
for me:
SQL select unit_cost,
to_number(unit_cost,'$999,999.99') from tomtest;
UNIT_COST
TO_NUMBER(UNIT_COST,'$999,999.99')
--$34,100.50
34100.5$34,000.05
34000.05
$9.25
9.25
SQL
What
version of
Title: To_Number
My
guess is that you have leading or trailing spaces. try
select
to_number(LTRIM(RTRIM(unit_cost)),'$999,999,999.99') from
elas.qdr
-Original Message-From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 05, 2002
2:24 PMTo: Multiple
Title: RE: To_Number
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
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
Database Administrator
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
To: Multiple recipients of list
ORACLE-L
Subject: RE: To_Number
My guess is that you have
leading or trailing spaces. try
select to_number(LTRIM(RTRIM(unit_cost)),'$999,999,999.99')
from elas.qdr
-Original Message-
From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]
Sent: Thursday
Yes, yes you are.
-Original Message-
Sent: Thursday, December 05, 2002 2:50 PM
To: Multiple recipients of list 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
by: Subject: Re: To_Number
[EMAIL PROTECTED]
om
Title: RE: To_Number
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
ORACLE-L
wisernet100@Y[EMAIL PROTECTED]
AHOO.COMcc:
Sent by: Subject: Re: To_Number
[EMAIL PROTECTED]
om
12/05
Did you check it with dump() ?
select dump(unit_cost,16) from tablewhatever;
for Hex. Check for unprintables that way. Try inserting the bad values
into a look-aside table for later analysis i.e.
create table look_aside (rownum rowid, bad_val varchar2(20),
dump_val varchar2(200));
declare
23 matches
Mail list logo
