tag:blogger.com,1999:blog-17155656.post115154172064913788..comments2023-04-06T12:09:13.895+02:00Comments on db4free.net blog: Sorting of numeric values mixed with alphanumeric valuesMarkus Popphttp://www.blogger.com/profile/15355530354397508921noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-17155656.post-77337080560511008762010-04-26T11:50:40.790+02:002010-04-26T11:50:40.790+02:00Hi, For the one character case:
....
ORDER BY
if...Hi, For the one character case:<br />....<br /><br />ORDER BY <br />if(version REGEXP ('^[0-9]') = 1,cast(version as UNSIGNED),999999) ASC, if(version REGEXP ('^[0-9]') = 1,'z',version) ASC<br /><br />....<br /><br />Regards,<br /><br />D.MinhAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-17155656.post-62166782054853991832009-05-11T10:25:00.000+02:002009-05-11T10:25:00.000+02:00I was looking for another approach:
ORDER BY LENG...I was looking for another approach:<br /><br />ORDER BY LENGTH(version), version<br /><br />Worked as well, but is limited to one char-addon. I like about your solution and implemented it right now.Stefanhttp://www.thelongrun.denoreply@blogger.comtag:blogger.com,1999:blog-17155656.post-21816467325287157402008-02-27T07:15:00.000+01:002008-02-27T07:15:00.000+01:00when i tried in postgresql I could not get the res...when i tried in postgresql I could not get the result.<BR/>I have unit number field is varchar thats shy i did not get results<BR/>when i run the query as<BR/>select unit_number from units order by unit_number<BR/>1 <BR/>10 <BR/>11 <BR/>2 <BR/>8 <BR/>A100 <BR/>MO2Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-17155656.post-82982797398147991062008-02-15T12:30:00.000+01:002008-02-15T12:30:00.000+01:00even i want to have an answer for sorting somethin...even i want to have an answer for sorting something like:<BR/>abc1<BR/>abc2<BR/>abc10<BR/>abc11<BR/><BR/>when tried in mysql querry, the<BR/>output is:<BR/>abc1<BR/>abc10<BR/>abc11<BR/>abc2<BR/> it would be thankful if any one can tell me how to sort it properly like:<BR/>abc1<BR/>abc2<BR/>abc10<BR/>abc11Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-17155656.post-72977126779570701242007-12-19T03:34:00.000+01:002007-12-19T03:34:00.000+01:00The web is a great place - just what I was looking...The web is a great place - just what I was looking for - <BR/>cheers Mate :) much appreciatedAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1151573971225685352006-06-29T11:39:00.000+02:002006-06-29T11:39:00.000+02:00I used to implicit cast by just adding +0 after th...I used to implicit cast by just adding +0 after the value: ORDER BY version+0, version. But CAST() is of course more clear.<BR/><BR/>I still miss a "natural sort" collation, so data that mix text and numbers could be sorted nicely, e.g. "User 9", "User 10", "User 11".Anonymousnoreply@blogger.com