Converting VARCHAR to DECIMAL values in MySql

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;

I think you need to try doing something like this on your MySQL if you have admin privilege on your MySQL.

ALTER TABLE tablename MODIFY columnname DECIMAL(M,D)

for the M,D variables, read this - http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html

And MySQL should be able to automatically converting a text to a numeric. Just that the data type in MySQL might not be a decimal yet that's why you can't store any decimal.


Be aware that if you convert from VARCHAR to DECIMAL and do not specify a precicision and maximum number of digits (i.e. DECIMAL instead of DECIMAL(5,2)) MySQL will automatically round your decimals to integer values.