Force Sorting Of VARCHAR Data In MySQL

18th September 2008 - 3 minutes read time

If you find that you are having trouble sorting data in a VARCHAR column in a MySQL database then you can try the following trick.

Lets say that you had the values 1,200,30,4000 and 5 and that you inserted them into the database in that order. When the following query is run on this data:

SELECT numbers FROM table ORDER BY numbers;

The following output is seen.

1
200
30
4000
5

This is clearly not the correct order, although it represents the order of. You can force a natural order to the sort by using a "+0" after the colum you are trying to sort by.

SELECT numbers FROM table ORDER BY numbers+0;

This produces the following output, which is sorted as you expect a set of numbers to be sorted.

1
5
30
200
4000

This is essentially the same as casting the number column, as in the following query.

SELECT number FROM test.tests ORDER BY CAST(number AS UNSIGNED);

However, what happens if you added some textual data? Let's take the original dataset and add two text items to it. These are "1,000" and "text". Using the default sort these two text items are unaffected and appear in the order in which they were added. When using the "+0" method anything that is slightly numeric will be placed into the list and anything else will get put at the start of the data.

text
1
1,000
5
30
200
4000

When using the cast method the order is exactly the same, but in this case MySQL throws some exceptions when trying to cast a non numeric value.

If you are storing numbers in your database table and have to use this query to get the correct order then you might want to consider altering the table so that these values are stored as integers.

Add new comment

The content of this field is kept private and will not be shown publicly.