Mysql – sort a varchar field numerically in thesql

MySQL

I have the varchar field named _tid, normally which contains numerical field, but it is also possible that may be contain string field. Now i wanted to to sort the column by numerically rather than lexicographically.
Here the query i've used,

mysql> select _tid,_name from teacher order by _tid;

which returns the following row set.

 +------+----------------------+
    | _tid | _name                |
    +------+----------------------+
    | 1    | A.MANIVANNAN         |
    | 10   | M.ELUMALAI           |
    | 100  | SAMPATH.R            |
    | 101  | S.PAULRAJ            |
    | 102  | A.ASHOK KUMAR        |
    | 103  | S.JAYAKUMAR          |
    | 104  | S.CINRAS             |
    | 105  | P.MURUGAN            |
    | 106  | S.VIJAY              |
    | 107  | N.KARTHIKEYAN        |
    | 108  | G.BALAKRISHNAN       |
    | 109  | C.THARANI            |
    | 11   | M.PONNUSAMY          |
    | 110  | J.KANNAN             |
    | 111  | V.MAHENDRAN          |

But i want row set like the following,

+------+----------------------+
| _tid | _name                |
+------+----------------------+
| 1    | A.MANIVANNAN         |
| 2    | A.PONNIVALAVAN       |
| 3    | B.MUTHU              |
| 6    | R.Kumar              |
| 4    | P.RAJAKALI           |
| 5    | N.Shaunmuga Sundram  |
| 8    | M.Balaji             |
| 7    | V.PALANI             |
| 9    | J.RANJITH            |
| 10   | M.ELUMALAI           |
| 11   | M.PONNUSAMY          |

Best Answer

Two possibilities: Either pad your values with zeros from the left and sort based on that:

SELECT _tid, _name FROM teacher ORDER BY lpad(_tid, 3, '0');
-- choose an appropriate number instead of 3

or cast your values to a number (similar to Aaron W's solution, apart from here I cast explicitly, and that is a clearer solution):

SELECT _tid,_name FROM teacher ORDER BY cast(_tid as decimal);

Be careful if you have non-number strings in your field:

SELECT cast('something' as decimal);
0
SELECT 'something' + 0;
0