Natural Sort in MySQL

Question

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 1

To order:
0
1
2
10
23
101
205
1000
a
aac
b
casdsadsa
css

Use this query:

SELECT 
    column_name 
FROM 
    table_name 
ORDER BY
    column_name REGEXP '^\d*[^\da-z&\.\' \-\"\!\@\#\$\%\^\*\(\)\;\:\\,\?\/\~\`\|\_\-]' DESC, 
    column_name + 0, 
    column_name;

Answer 2

If you do not want to reinvent the wheel or have a headache with lot of code that does not work, just use Drupal Natural Sort ... Just run the SQL that comes zipped (MySQL or Postgre), and that's it. When making a query, simply order using:

... ORDER BY natsort_canon(column_name, 'natural')

Answer 3

1. Add a Sort Key (Rank) in your table. ORDER BY rank

2. Utilise the "Release Date" column. ORDER BY release_date

3. When extracting the data from SQL, make your object do the sorting, e.g., if extracting into a Set, make it a TreeSet, and make your data model implement Comparable and enact the natural sort algorithm here (insertion sort will suffice if you are using a language without collections) as you'll be reading the rows from SQL one by one as you create your model and insert it into the collection)

Answer 4

Another option is to do the sorting in memory after pulling the data from mysql. While it won't be the best option from a performance standpoint, if you are not sorting huge lists you should be fine.

If you take a look at Jeff's post, you can find plenty of algorithms for what ever language you might be working with. Sorting for Humans : Natural Sort Order

Answer 5

Just found this:

SELECT names FROM your_table ORDER BY games + 0 ASC

Does a natural sort when the numbers are at the front, might work for middle as well.

Answer 6

If you're using PHP you can do the the natural sort in php.

$keys = array();
$values = array();
foreach ($results as $index => $row) {
   $key = $row['name'].'__'.$index; // Add the index to create an unique key.
   $keys[] = $key;
   $values[$key] = $row; 
}
natsort($keys);
$sortedValues = array(); 
foreach($keys as $index) {
  $sortedValues[] = $values[$index]; 
}

I hope MySQL will implement natural sorting in a future version, but the feature request (#1588) is open since 2003, So I wouldn't hold my breath.