This is all about db4free.net and the database systems that it offers. The main focus are practical examples of the new features of MySQL 5 and 6 that should show you how to use them for your advantage in your daily life ;-).
Thursday, June 29, 2006
Sorting of numeric values mixed with alphanumeric values
even i want to have an answer for sorting something like: abc1 abc2 abc10 abc11
when tried in mysql querry, the output is: abc1 abc10 abc11 abc2 it would be thankful if any one can tell me how to sort it properly like: abc1 abc2 abc10 abc11
when i tried in postgresql I could not get the result. I have unit number field is varchar thats shy i did not get results when i run the query as select unit_number from units order by unit_number 1 10 11 2 8 A100 MO2
I used to implicit cast by just adding +0 after the value: ORDER BY version+0, version. But CAST() is of course more clear.
ReplyDeleteI 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".
The web is a great place - just what I was looking for -
ReplyDeletecheers Mate :) much appreciated
even i want to have an answer for sorting something like:
ReplyDeleteabc1
abc2
abc10
abc11
when tried in mysql querry, the
output is:
abc1
abc10
abc11
abc2
it would be thankful if any one can tell me how to sort it properly like:
abc1
abc2
abc10
abc11
when i tried in postgresql I could not get the result.
ReplyDeleteI have unit number field is varchar thats shy i did not get results
when i run the query as
select unit_number from units order by unit_number
1
10
11
2
8
A100
MO2
I was looking for another approach:
ReplyDeleteORDER BY LENGTH(version), version
Worked as well, but is limited to one char-addon. I like about your solution and implemented it right now.
Hi, For the one character case:
ReplyDelete....
ORDER BY
if(version REGEXP ('^[0-9]') = 1,cast(version as UNSIGNED),999999) ASC, if(version REGEXP ('^[0-9]') = 1,'z',version) ASC
....
Regards,
D.Minh