[DB] How to sort non-numeric field numerically in MySQL

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]