MySQL Natural Sort Order By on Non-Numeric Field Type
I recently worked on a project where I had to sort a set of rows returned from a MySQL query. The problem is that most of the data in the field being sorted is numeric yet the field type is varchar since some of the entries contained characters.
The reason that this is a problem is that MySQL sorts character fields using a method that will produce undesirable results with numeric data. For example, sorting 4, 10, and 50 as character data produces 10, 4, and 50. In most applications, this is highly undesirable.
The solution to this is to force a sorting order that is commonly referred to as a natural sort. Natural sort is just a term that refers to how humans would commonly sort a set of information (numbers as numbers and non-numeric characters alphabetically). Fortunately, this isn’t difficult to achieve in MySQL.
To further illustrate the problem, here is a simple table description:
> DESC SampleData;
| Field | Type |
|---|---|
| data_char | varchar(5) |
I filled up the table with sample data. The following example query and resulting data shows the problem clearly:
> SELECT * FROM SampleData ORDER BY data_char;
| data_char |
|---|
| 10 |
| 11 |
| 120 |
| 21 |
| 3 |
| a1 |
As you can see, the results aren’t exactly usable. If we simply modify the order by declaration slightly (add “+0″ to the order by field), you can force MySQL to sort the field naturally.
> SELECT * FROM SampleData ORDER BY data_char+0;
| data_char |
|---|
| 3 |
| 10 |
| 11 |
| 21 |
| 120 |
| a1 |
There you have it. To force a natural sort, just add a 0 onto the field you wish to be naturally sorted.
Tags: MySQL, TipsRelated Posts
Receive Updates
New posts on chrisjean.com delivered directly to your email.
this method only works for numeric, does not work for strings with numeric values
does not work:
a10,a11,a120,a21,a3,aa1
also does not work:
10a,10c,10b
also I believe a1 comes first in your list not last, since a1 casts to int 0
Yes. I’ve since found this out. My “trick” worked for the initial data sets that I needed it for, but it obviously doesn’t work as expected for all (most?) datasets.
For more than a year now I have revisited this problem off and on and have yet to find a better (read “actual”) solution to this need without using code to reorder the results.