January 16, 2007 at 8:04 pm
Dear All,
I have a field for example A with data type varchar. I want to sum the field using SUM(CONVERT(FLOAT, A)) but it always display error: Error converting data type varchar to float.
I checked and it turns out that there are several data that have comas (,) and dots (.) in it. I have tried SUM(CONVERT(FLOAT, REPLACE(A,',','')) to replace the comma and the same way to replace the dots but the error still occur.
I cannot change the data or the data type coz, field A doesnt only contains number, and the data are live data.
Please help me.
Many thanks before.
Widya Hapsari
myeagles.multiply.com
January 17, 2007 at 1:20 am
Hi,
you say that column A does not always contain only numbers - that means you can not convert it to FLOAT (or any other number datatype). What other values can there be in the column, can you post some typical examples? What is the column A used for and why do you want to sum it? And how? What is the sum of 'Dixon Avenue 15' and 124.38?
It does not make sense to sum a column that sometimes contains values like software version numbers, IP addresses or maybe just plain character (non-numeric) values.
There are several ways to deal with it, but all result in rejecting part of the rows (or transforming non-numeric values to NULL). Either simply filter out the rows that contain "wrong" (not convertible) values, or use more complicated REPLACE statement to "clean" the data, so that it can be converted to number. Or maybe you can create another column with some number datatype (float or numeric), computed from your column A... and use this new column for calculations.
January 17, 2007 at 1:45 am
yes, i have "clean" the non numeric data by adding the where keyword. [the A in this wr_id contains only numeric data but in varchar type]
so here's what i have done (which still have the same error).
select CONVERT(FLOAT, REPLACE(A,',',''))
from run_tcontainer
where wr_id = 10581
Please advice me....
ASAP
Widya Hapsari
myeagles.multiply.com
January 17, 2007 at 1:57 am
Hi Widya,
we need some data to test this. Please post a CREATE TABLE statement (you can include just the 2 columns, wr_id and A) and INSERT INTO with some sample data that will reproduce the same effect you encountered.
Obviously, WHERE wr_id=10581 is not enough to clean the data. You still have some values in column A that can't be converted. Unfortunately you didn't answer any of my questions and I really don't want to guess blindly, what could be the problem and what to do with it.
Use ISNUMERIC function for a quick check, or LIKE with a search pattern to identify the offending rows.
select *
from run_tcontainer
where wr_id = 10581
AND ISNUMERIC(A)=0
(This will NOT find all problems always, but is handy for a quick check of situation)
January 17, 2007 at 3:03 am
thanks Vladan. I have run that and i have found particular data that cause the trouble. Some of those are in the forms of: '230453,50' and '1.000.000'
See, this suppose to be money and i want to calculate the total amount of the money.
but the user just put the number in any way they want to. these are live data and i cant just change it
However, this is CREATE TABLE STATEMENT:
create table total
( cd_id int,
wr_id int,
A varchar(100)
)
insert into total (cd_id ,wr_id, A) values (1,10581,'325,000')
insert into total (cd_id ,wr_id, A) values (2,10581,'230453,50')
insert into total (cd_id ,wr_id, A) values (3,10581,'200.000')
insert into total (cd_id ,wr_id, A) values (4,10581,'160.533')
insert into total (cd_id ,wr_id, A) values (5,10581,'417.000,-')
===================================================
what i want is to sum up field A [total money that the user will get].
wr_id=10581 is a foreign key which signed that A contains how much amount of money that a user will get.
anything else i should add?
many thanks for your help
Widya Hapsari
myeagles.multiply.com
January 17, 2007 at 3:31 am
My first guess was
SELECT SUM(CAST(REPLACE(STUFF(a.A,LEN(a.A)-CHARINDEX(',',REVERSE(a.A))+1,1,'.'),',','') as float))
FROM (SELECT REPLACE(CASE WHEN CHARINDEX('-',A) > 0 THEN '-' ELSE '' END + REPLACE(A,'-',''),'.',',') AS [A] FROM [total]) a
But
what does '325,000' equal? 325000 or 325.0
what does '230453,50' equal? 230453.50 !
and what about '417.000,-' -417.0 or -417000 ?
Far away is close at hand in the images of elsewhere.
Anon.
January 17, 2007 at 3:33 am
Thanks.
If I would be asked to sum values of this type, I would refuse to do it, because there is no way to do it, unless all inputs follow the same pattern (of course best would be a money datatype, but varchar can do if entries are correct). There is no way how to explain to any software what the user meant when entering the data.
Sometimes there are no decimal places at all, sometimes "," delimits thousands and "." decimals, sometimes it is the other way round or everything is delimited with the same... sorry, no program can cope with this. And you even don't know what will they write next!
Maybe you could write code that translates successfully 90% or even 99% percent, but you will never guarantee that the result is correct. You shouldn't allow that, especially if it is about money. Be polite but strict - good manager should understand that and support your request : to change the way data are entered, so that there can be no mistake about what the user meant (of course, this would also include a tedious task of verifying and correcting those existing data that don't pass the test).
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy