sort a varchar field numerically in 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          |

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

I’ve gotten around this before by adding zero to the column

select _tid,_name from teacher order by _tid + 0

edit

This had me thinking a bit more so I decided to run a simple test for speed. I’m sure the way you choose depends on the values you have in your database and how you want it to sort, but I created the following table and then inserted 100,000 random teacher names. Using a Windows 7 Pro 32-bit, MySQL 5.5.8

CREATE TABLE `teacher` (
  `_tid` varchar(255) NOT NULL,
  `_name` varchar(255) NOT NULL,
  PRIMARY KEY (`_tid`)
) ENGINE=InnoDB;

I ran each query ten times and averaged the query time

select _tid,_name from teacher order by _tid + 0;
-- 0.60ms

SELECT _tid, _name FROM teacher ORDER BY lpad(_tid, 5, '0');
-- 0.68ms

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

I’m sure the results varied because I have a couple VM’s running along with only having 250MB – 500MB of available RAM, but it was a quick test to see if there was much of a difference.

I have used dezso’s padding method in the past, but not too long ago I found a simple solution, which was to sort the field first using its length, then its value:

SELECT _tid,_name FROM teacher ORDER BY length(_tid), _tid;

which causes single digit values to sort before double digit values, and double digit to sort before 3-digit, and so on. I have not bench-marked the performance however.