You cant do a natural sort on a string column in MySQL post

Comments

Recently a question popped up over on the CakePHP google group where someone had a problem sorting on a column of "registration numbers". This was his table sorted order by "Reg No ASC":

ID Name Reg No
1 Product A 1
2 Product B 123
3 Product C 2

1, 123, 2. That's not correct right? Well it is if we are sorting strings! The problem here is that MySQL will not do a "natural sort" on a text type column like char, varchar, or text. The solution to this is to change the column type to a numeric value, especially considering he is storing numbers anyway! Once you change "Reg No" to int, the column will sort correctly.

ID Name Reg No
1 Product A 1
3 Product C 2
2 Product B 123

Always remember to check your schema!

  • Tags:
  • mysql
  • natural sort
  • natural sorting
  • sql

explosive web programming MODERN CODE TACTICS

by James Fuller