Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

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

share|improve this answer

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
share|improve this answer

Try that snippet

SELECT * FROM file ORDER BY ID + 0 ASC
share|improve this answer
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().

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.12.27.2141\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </no"], "copyright__copyright__copyright": ["rove this answer\nSELECT * FROM file\nORDER BY CONVERT(SUBSTRING(ID,5),UNSIGNED) ASC\n\nSUBSTRING() will extract all characters after 'MDT ' and CONVERT() will change remaining substring to unsigned integer on which ORDER BY is performed\n\nnote SUBSTR() is a synonym for SUBSTRING().\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}