How to do natural alpha-numeric sort in MySQL
Published February 1st, 2006 in Ruby on Rails, EducationLooking through the records in the Teacher! database, I realized that some people are using numbers instead of names to represent their students. That’s fine by me; one of my design objectives with Teacher! is to get the application out of the way. That means it doesn’t care how you choose to refer to your students, and it doesn’t force you to do it in any particular way.
So if you want to just use first names, just use last names, just use number or use some other wacky combination, it doesn’t matter. The application doesn’t choke just because you decided to call one of your students R2D2 (hey, whatever floats your boat).
But the numeric student names weren’t sorting correctly in the class lists. Because Teacher! was expecting a text string, not a number, it was sorting by alpha order, not numerically. That’s no good.
So I did a little digging and came up with an answer. You can force a MySQL SELECT statement to sort naturally (first by letters and then by numbers) by doing this:
SELECT names FROM your_table ORDER BY names + 0 ASC
The added “0” forces it to sort the way you want it to, but doesn’t alter your data in any way. Pretty neat!
For those of you using Ruby On Rails, you can define the sort order in your model. So, for example, in Teacher!, I have a Classroom model that has_and_belongs_to_many => :students. So in the classroom.rb model I have this:
class Classroom < ActiveRecord::Base
has_and_belongs_to_many :students,:order => 'firstname + 0 ASC'
...
end
That way my :classroom objects always have students collections that are sorted alphanumerically. Hurray!
<—3a60a642374c64a449e206fa2e7064e1—>
17 Responses to “How to do natural alpha-numeric sort in MySQL”
- 1 Trackback on Mar 28th, 2007 at 8:03 pm
- 2 Trackback on Aug 5th, 2008 at 12:12 pm
Worked for me, thanks!
What about when you are concatanating fields… eg
select concat(first name, last name) from names order by ???
what do you put in the question marks??
Well, it depends on how you want to order your result. If you want to order by last name, you’d put “order by last_name”. If you wanted to order by the concatenated field, you’d do “SELECT concat(first_name, last_name) as full_name ORDER by full_name. In either case you can add ” 0” to force MySQL to sort naturally.
It doesn’t sort completely naturally…any idea on how to get it to sort completely naturally.
For example, most people could consider A1, A2, A3…A10, A11 to be a natural order, but 0 sorts this as A1, A10, A11, A2, A3
Any ideas?
Also, besides what Jeremy said, this little “fix” will sort alpha-based entries first, and only then numeric.
For example if you have the following entries in the database:
1, A1, 11, A11, 2, A2
The result will be (ASC):
A1, A11, A2, 1, 2, 11
But the expected result supposed be:
1, 2, 11, A1, A2, A11
So basicaly this fix only works if you have ONLY numeric entries…
Thanks !
It worked in sorting Ip addresses
Thank you, this solution worked perfectly.
Just what i was looking for. Thanks for the rails example!
You saved my day!!
Thnx, Bart
Worked exactly how i wanted it to! thnx
A great little tip which has saved me a lot of hassle on a CMS that I’m working on!
Thanks
Jonathan
To solve the “A1, A11, A2, 1, 2, 11” problem a bit better (although sill not perfect) I use the following:
order by LPAD ASC
where 25 is the length of your varchar field.
The left-pads the string with spaces so that shorter strings are always above longer ones.
This should product the expected result of 1,2,11,A1,A2,A11
(mostly)
Johan
I had the same problem but with ponctuation (i.e Q-345-5678, Qchl-5448-7667…)
i.e of order with normal ORDER BY clause————————————Q-345-5678
Q-645-5666
Qchl-5448-7667
Qchl-625-777
I came out with this
SELECT * FROM tblPierres WHERE CategoryID=’$CategoryID’ ORDER BY SUBSTRING_INDEX(QuartzID,’-’,1), SUBSTRING_INDEX(QuartzID,’-’,-2) 0 ASC
Lets look at the query—————————————**FIRST==
SUBSTRING_INDEX(QuartzID,’-’,1)
**Isolate the alpha from numeric string and look at Alpha
i.e of order (Same as normal query)——————-
Q-345-5678
Q-645-5666
Qchl-5448-7667
Qchl-625-777
**SECOND==
SUBSTRING_INDEX(QuartzID,’-’,-2)
**Isolate the alpa from numeric string and order it by Numeric
So first it orders it by Alpha then takes the result and look at Numeric
i.e of order (No change from previous)——————-
Q-345-5678
Q-645-5666
Qchl-5448-7667
Qchl-625-777
But now with 0 ASC, this is where its happen :
It Takes all alpha and order it
and takes all numeric value within the same alpha and order it
i.e of final order——————-
Q-345-5678
Q-645-5666
Qchl-625-777
Qchl-5448-7667
Thankx, the solution works perfectly
!
Really cool!! Thanks.