It is common to store numeric values on Varchar or non-numeric field for developer’s convenience. However, it can cause a problem when sorting. For example, 10 comes prior to 9 when sorted.
Here are three tricks thatcan be found on various websites as below.
1. Casting
It is possible to sort while casting non-numeric value to numeric. Values must be all numeric, otherwise value will be casted as “0”.
[code language=”php”]
SELECT * from employee ORDER BY CASE(emp_number as DECIMAL);
[/code]
2. Left padding zeros
Padding zeros to the left will make all values to be sorted as same as numerically.
[code language=”php”]
SELECT * from employee ORDER BY lpad(emp_number, 10, 0);
[/code]
3. Coerced Casting
By using numeric operation, it is possible to cast non-numeric value to numeric, Then, it can be sorted by numerically.
[code language=”php”]
SELECT * from employee ORDER BY emp_number + 1;
[/code]