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?
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 that snippet
SELECT * FROM file ORDER BY ID + 0 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 
SELECT * FROM file ORDER BY CONVERT(SUBSTRING(ID,5),UNSIGNED) ASC SUBSTRING() will extract all characters after 'MDT ' and CONVERT() will change remaining substring to unsigned integer on which ORDER BY is performed
note SUBSTR() is a synonym for SUBSTRING().
I was searching it out too, but just while reading over here I got it striked in my head and found one solution that if that column is having only numbers as data then you need to make modification in database table and define that column as INT value type, then I am sure what you need will be done. Eg. {SELECT * FROM file ORDER BY CONVERT(SUBSTRING(ID,5),UNSIGNED) ASC} in such case Convert is column and that needs to be defined as INT(5) will work.
