Is there an elegant way to have performant, natural sorting in a MySQL database?
For example if I have this data set:
- Final Fantasy
- Final Fantasy 4
- Final Fantasy 10
- Final Fantasy 12
- Final Fantasy 12: Chains of Promathia
- Final Fantasy Adventure
- Final Fantasy Origins
- Final Fantasy Tactics
Any other elegant solution than to split up the games' names into their components
- Title: "Final Fantasy"
- Number: "12"
- Subtitle: "Chains of Promathia"
to make sure that they come out in the right order? (10 after 4, not before 2).
Doing so is a pain in the a** because every now and then there's another game that breaks that mechanism of parsing the game title (e.g. "Warhammer 40,000", "James Bond 007")
Regarding the best response from Richard Toth https://stackoverflow.com/a/12257917/4052357
Watch out for UTF8 encoded strings that contain 2byte (or more) characters and numbers e.g.
Using MySQL's
LENGTH()
inudf_NaturalSortFormat
function will return the byte length of the string and be incorrect, instead useCHAR_LENGTH()
which will return the correct character length.In my case using
LENGTH()
caused queries to never complete and result in 100% CPU usage for MySQLp.s. I would have added this as a comment to the original but I don't have enough reputation (yet)
Add a field for "sort key" that has all strings of digits zero-padded to a fixed length and then sort on that field instead.
If you might have long strings of digits, another method is to prepend the number of digits (fixed-width, zero-padded) to each string of digits. For example, if you won't have more than 99 digits in a row, then for "Super Blast 10 Ultra" the sort key would be "Super Blast 0210 Ultra".
Same function as posted by @plalx, but rewritten to MySQL:
Usage:
I have tried several solutions but the actually it is very simple:
Also there is natsort. It is intended to be a part of a drupal plugin, but it works fine stand-alone.
I think this is why a lot of things are sorted by release date.
A solution could be to create another column in your table for the "SortKey". This could be a sanitized version of the title which conforms to a pattern you create for easy sorting or a counter.