Sort by numeric values ​​in SQL ascending

Question

I am trying to get this conclusion.

MDT 1 MDT 2 MDT 3 MDT 11 MDT 44 

but, the values ​​are sorted alphabetically, so 123 to 2.

example:

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

etc.

I am using this code, but it does not seem to work.

 SELECT * FROM file ORDER BY ABS(ID) ASC 

How can i solve this?

Answer 1

If your identifier will always contain a prefix as MDT , you can use it to sort according to your requirement:

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

SQLFiddle demo

Answer 2

Try. In this case, it will be sorted by number:

 select substr(id,4)*1 from file order by substr(id,4)*1 

He will give

one
2
3
eleven
44
...

If you want all fields to try to execute the following query ( "substr (id, 4)" or "substr (id, 5)" depending on the length of the string (for example: id = proj-001911 → accept SUBSTR (id, 6 ) * one))

 select * from file order by substr(id,4)*1 

Answer 3

Try this snippet

 SELECT * FROM file ORDER BY ID + 0 ASC 

Answer 4

 SELECT * FROM file ORDER BY CONVERT(SUBSTRING(ID,5),UNSIGNED) ASC 

SUBSTRING() will extract all characters after 'MDT ' and CONVERT() will change the remaining substring to an unsigned integer on which ORDER BY

note SUBSTR() is synonymous with SUBSTRING() .

Answer 5

I also looked for it, but just reading here, I hit it in my head and found one solution: if this column contains only numbers as data, you need to make changes to the database table and define this column as an INT value. type, then I'm sure you will need to do. For example. {SELECT * FROM file ORDER BY CONVERT(SUBSTRING(ID,5),UNSIGNED) ASC} in this case Convert is a column, and it must be defined as INT(5) .