Thursday, June 29, 2006

Sorting of numeric values mixed with alphanumeric values

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/06/sorting-of-numeric-values-mixed-with-alphanumeric-values/.
Posted by at

6 comments:

Anonymous said...

I used to implicit cast by just adding +0 after the value: ORDER BY version+0, version. But CAST() is of course more clear.

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".

11:39
Anonymous said...

The web is a great place - just what I was looking for -
cheers Mate :) much appreciated

03:34
Anonymous said...

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

12:30
Anonymous said...

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

07:15
Stefan said...

I was looking for another approach:

ORDER BY LENGTH(version), version

Worked as well, but is limited to one char-addon. I like about your solution and implemented it right now.

10:25
Anonymous said...

Hi, For the one character case:
....

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

11:50

Post a Comment

Subscribe to: Post Comments (Atom)