Converting VARCHAR to DECIMAL values in MySql

Question

I have imported a CSV file that contains string values (eg.eating) and floating values (eg. 0.87) into a table in my phpMyAdmin database. After I get ride of all the string values and retain only the rows that have the decimal values, I need to convert such values from VARCHAR to DECIMAL/FLOAT so that I can perform a MAX() on this attribute.

How do I do this? Each time I try doing this through the GUI in phpMyAdmin, all my values are automatically rounded off to 0 and 1s.

Please help me!

Answer 1

Without Converting you can find Maximum using this query

select max(cast(stuff as decimal(5,2))) as mySum from test;

check this SQLfiddle

your demo table:

create table test (
   name varchar(15),
   stuff varchar(10)
);

insert into test (name, stuff) values ('one','32.43');
insert into test (name, stuff) values ('two','43.33');
insert into test (name, stuff) values ('three','23.22');

Your Query:

For SQL Server, you can use:

select max(cast(stuff as decimal(5,2))) as mySum from test;