Sky Blue Sofa Blog How to Implement Natural Sorting in MySQL - Sky Blue Sofa Blog

How to Implement Natural Sorting in MySQL

Posted by Dave Rogers // December 20, 2013 // in Blog // 0 Comments

Cable ends

In the programming world, Natural Sorting is taking a list of things and sorting them, well, the way a human naturally would. Take this list, sorted in natural order, for example:

col_name
--------------- 
test1
test2
test3
test4
test5
test6
test7
test8
test9
test10
test11
test12

MySQL sorting doesn't work, naturally

In PHP, you could use the natsort() function to sort an array to return an alphanumeric list in the order given above. But we're talking about MySQL here. In MySQL we would normally run this query:

SELECT col_name 
FROM table
ORDER BY col_name;

However, that would give us these results:

col_name
--------------- 
test1
test10
test11
test12
test2
test3
test4
test5
test6
test7
test8
test9

Oooh, now that's better, but not quite

Not exactly what we're looking for, so I asked the Google and found this webpage. It describes how to do a natural sort in MySQL. To do so, run this query:

SELECT col_name 
FROM table
ORDER BY col_name+0<>0 DESC, col_name+0, col_name;

The above query works great 99% of the time. The only time I found it to fail was if we had a row with a col_name value of '0' (zero). Let me give you some real world data (kinda):

col_name
---------------
0
18 Swans
Pigeons & Turtledoves
43 Parrot
Bluejays 

If we ran the query above, the result would be almost what we're looking for:

col_name
---------------
18 Swans
43 Parrot
0
Bluejays
Pigeons & Turtledoves

A bit uglier, but works better

But you can see that the '0' value is in the wrong place. It should be at the top of the list. What to do? Well, I came up with a variation on the query I got earlier. It's not quite as pretty and might take a bit longer, but it will actually do the job:

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

Now the results come back just the way they should:

col_name
---------------
0
18 Swans
43 Parrot
Bluejays
Pigeons & Turtledoves

The wrap-up

Which query you choose really depends on the type of data that you have in the column you're sorting:

  • All numerical or all alphabetical data:
    Use the built-in MySQL 'ORDER BY col_name' clause
  • If you have alphanumeric data and can guarantee that you won't have a 'zero' in the column:
    Use the easier 'ORDER BY col_name+0<>0 DESC, col_name+0, col_name' clause
  • If you have alphanumeric data but cannot guarantee that it won't have a 'zero' value:
    Use the regex 'ORDER BY col_name REGEXP '^\d*[^\da-z&\.\' \-\"\!\@\#\$\%\^\*\(\)\;\:\<\>\,\?\/\~\`\|\_\-]' DESC, col_name+0, col_name' clause

Have you implemented this? Any notes, issues or concerns? Let me know in the comments.

About the Author

Dave Rogers

Dave is the founder of Sky Blue Sofa Web Design. He enjoys working out, spending time with his wife and dogs and programming. He grew up and currently resides in the Illinois Quad Cities. You can find his personal blog at strength/reliance.com.

View Profile »

Comments

You must be logged in to leave a reply. Login »