Natural Sort in MySQL

BlaM picture BlaM · Sep 30, 2008 · Viewed 81.7k times · Source

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

Answer

slotishtype picture slotishtype · Jun 14, 2011

Here is a quick solution:

SELECT alphanumeric, 
       integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric