Order by numeric values in SQL ascending

Question

I'm trying to get this output.

MDT 1
MDT 2
MDT 3
MDT 11
MDT 44

but, The values are ordered alphabetically, so 123 comes before 2.

example :

MDT 1
MDT 11
MDT 156
MDT 2
MDT 3
MDT 303
MDT 44

and so on.

I'm use this code, but it seem didn't work.

SELECT * FROM file ORDER BY ABS(ID) ASC

How can I solve this?

Answer 1

If your ID is always going to contain the prefix as MDT, then you can use this, to sort as per your requirement:

SELECT * FROM File 
ORDER BY CAST(replace(ID, 'MDT ', '') AS UNSIGNED) ASC

SQLFiddle demo

Answer 2

Try that snippet

SELECT * FROM file ORDER BY ID + 0 ASC