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:
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):
Be careful if you have non-number strings in your field: