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.
Did I help you?
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.
Good Mysql Tip, thank very much : )
If the length of the alphabetic portion of the string is constant, like it was in my case, you can use something like this:
ORDER BY SUBSTR(name FROM 1 FOR 6), CAST(SUBSTR(name FROM 7) AS UNSIGNED), SUBSTR(machine.name FROM 7)
Notice that the ordering will only be different from a straight alphabetic ordering when there are numeric digits starting at character 7.
So, a list like [‘string123’, ‘string5’, ‘string20’] which would normally be sorted as [‘string123’, ‘string20’, ‘string5’], will instead be sorted as [‘string5’, ‘string20’, ‘string123’].
HI,
Its working for me…
Thanks
Thank you very much, it worked for me.
Thanks! It works, and it helps!
Thank you very much,It worked very vell.
Thanks a lot it was very helpfull
For sort string with nums just use “field + 0 ASC, field ASC” and it be better
In my case data length was not constant and using just +0 at the end still does not fix the natural order issue properly with alphanumeric data, however after much searching I eventually found an ORDER BY criteria that did work and may help others with similar problem.
ORDER BY data_char+00 DESC, data_char +0, data_char
Thanks Kevin, you saved my day
Thanks. working like a charm..
I noticed that ordering in DESC works fine, so I just call array_reverse in php when I get the result.