MySQL: Sort Number in String Format

TL;DR: use key_name * 1; like

 select id, name, age from users order by salary * 1

Well, it’s better to store data in a database in a number format. But there is always an accident, sometimes you or your colleague just stored some numbers in the database with string format, of course for some reason.

And one day you need to get data from that table and sort the result by that string-number column. Then you find something is not right.

If you run a command like this:

select "1000" > "2"

you will find that the result is 0. it means, 100 < 2. that’s because MySQL just compares these 2 values in a string. thus 2 > 1.

There are several work around to make this works, like use conv(). So write your SQL like this:

select * from users order by conv(`salary`, 10, 10)

conv(original_value, from_base, to_base) is a MySQL function, to convert a value from one base to another base, as you can also convert a numeric base system 2 to a numeric base system 10.

But there is another shorter and cooler way to do this. that’s mul the value by 1. Like so:

select * from users order by salary * 1

It works like a charm.

Need to notice that if the value is too large, the result will be in Scientific notation like 1e17.