Natural Sorting in MySQL

Working Around MySQL's Lack of Built-In Support for Natural Sorting

Several times in the last few projects I've built, I've come across the need to sort alphanumeric rows in a database by number (i.e. entry1, entry2, entry3, etc.). If you've ever tried to do this, you know that sorting entries that are alphanumeric is a pain.

The Data

For our purposes, we'll be using the following table:

Table: sorting_test
+--------------------------+-------------+
| alphanumeric VARCHAR(75) | integer INT |
+--------------------------+-------------+
| test1                    | 1           |
| test2                    | 2           |
| test3                    | 3           |
| test4                    | 4           |
| test5                    | 5           |
| test6                    | 6           |
| test7                    | 7           |
| test8                    | 8           |
| test9                    | 9           |
| test10                   | 10          |
| test11                   | 11          |
| test12                   | 12          |
+--------------------------+-------------+

The Problem

Sorting by the column integer, of course, presents no problems.

Query: SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY integer
+--------------------------+-------------+
| alphanumeric VARCHAR(75) | integer INT |
+--------------------------+-------------+
| test1                    | 1           |
| test2                    | 2           |
| test3                    | 3           |
| test4                    | 4           |
| test5                    | 5           |
| test6                    | 6           |
| test7                    | 7           |
| test8                    | 8           |
| test9                    | 9           |
| test10                   | 10          |
| test11                   | 11          |
| test12                   | 12          |
+--------------------------+-------------+

However, when we try sorting by the alphanumeric column, we get unexpected results:

Query: SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY alphanumeric
+--------------------------+-------------+
| alphanumeric VARCHAR(75) | integer INT |
+--------------------------+-------------+
| test1                    | 1           |
| test10                   | 10          |
| test11                   | 11          |
| test12                   | 12          |
| test2                    | 2           |
| test3                    | 3           |
| test4                    | 4           |
| test5                    | 5           |
| test6                    | 6           |
| test7                    | 7           |
| test8                    | 8           |
| test9                    | 9           |
+--------------------------+-------------+

Obviously, this is not the desired outcome. Since we're sorting alphabetically, the entries are actually in the correct order, but we need to find a way to sort numerically.

The Solution

There are a whole lot of solutions out there if you hit up Google for "natural sorting in MySQL." The one I've found to be the most straightforward is to sort by length first, then the column value.

Query: SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY LENGTH(alphanumeric), alphanumeric
+--------------------------+-------------+
| alphanumeric VARCHAR(75) | integer INT |
+--------------------------+-------------+
| test1                    | 1           |
| test2                    | 2           |
| test3                    | 3           |
| test4                    | 4           |
| test5                    | 5           |
| test6                    | 6           |
| test7                    | 7           |
| test8                    | 8           |
| test9                    | 9           |
| test10                   | 10          |
| test11                   | 11          |
| test12                   | 12          |
+--------------------------+-------------+

This works because the first nine entries are 5 characters long, with the tenth, eleventh, and twelfth entries at 6 characters; this creates two groups of entries that are then sub-sorted, resulting in our desired order.

Summary

This is a quick tip, but a useful one. Do you have a better way of forcing natural sorting in MySQL? Let me know in the comments!

Posted Sep 29, 2009 by Jason Lengstorf.
This entry is filed under mysql, sorting, and quick tip.