mysql sort string number

Question

I have a column of type varchar that stores many different numbers. Say for example there are 3 rows: 17.95, 199.95 and 139.95.How can i sort that field as numbers in mysql

Answer 1

If you need to sort a char column containing text AND numbers then you can do this.

tbl contains: 2,10,a,c,d,b,4,3

select * from tbl order by number_as_char * 1 asc, number_as_char asc 

expected output: 2,3,4,10,a,b,c,d

If you don't add the second order by argument only numbers will be sorted - text actually gets ignored.

Answer 2

Quickest, simplest? use * 1

select * from tbl order by number_as_char * 1 

The other reasons for using * 1 are that it can

1. survive some horrendous mishaps with underflow (reduced decimal precision when choosing what to cast to)
2. works (and ignores) columns of purely non-numeric data
3. strips numeric portions of alphanumeric data, such as 123A, 124A, 125A