Order by numeric values in SQL ascending

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

SQLFiddle demo

---

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 ID + 0 ASC

链接地址: http://www.djcxy.com/p/11562.html

上一篇: 共享存储在内部存储器中的图像

下一篇: 按SQL升序中的数值排序