Force Sorting Of VARCHAR Data In MySQL

18th September 2008

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:

  1. <p>The following output is seen.</p>
  2.  
  3. <pre language=">1
  4. 200
  5. 30
  6. 4000
  7. 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.

  1. <p>This produces the following output, which is sorted as you expect a set of numbers to be sorted.</p>
  2.  
  3. <pre language=" plain="">1
  4. 5
  5. 30
  6. 200
  7. 4000

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

  1. <p>However, what happens if you added some textual data? Lets 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.</p>
  2.  
  3. <pre language=">text
  4. 1
  5. 1,000
  6. 5
  7. 30
  8. 200
  9. 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.