Order by numeric values in SQL ascending
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
Try Like this it will sort based on numeric :
select substr(id,4)*1 from file order by substr(id,4)*1
It will gives
1
2
3
11
44
...
If You want all fields try the below query ("substr(id,4)" or "substr(id,5)") based on your string length (ex: id= proj-001911 --> take SUBSTR( id, 6 ) *1) )
select * from file order by substr(id,4)*1