MYSQL:Ordering A TextField Numerically

MYSQL:Ordering A TextField Numerically

While ordering the numeric values stored in a text field(having DataType as VarChar,Char,Text etc) in a MySQL table ,you may not be able to get the expected result. For example if your table contains a field “NUMBERS” which is of varchar type and having the values 1, 2, 3, 4, 5 upto 10.Then the query “SELECT NUMBERS FROM TestTable ORDER BY NUMBERS “ Would return the result as NUMBERS 1 10 2 3 4 5 6 7 8 9

So to get the numerically sorted result we have to query “SELECT NUMBERS FROM TestTable ORDER BY NUMBERS + 0” This will return the resultset as NUMBERS 1 2 3 4 5 6 7 8 9 10 Here +0 forces a numeric sort and to sort in ascending or descending order you have to specify ASC or DESC accordingly. By default the sort mode is ascending. For ascending order the query is: SELECT NUMBERS FROM TestTable ORDER BY NUMBERS + 0 ASC And For descending order the query will be: SELECT NUMBERS FROM TestTable ORDER BY NUMBERS + 0 DESC