Sort VARCHAR as INT in MySQL query

Published on May 7, 2009 in Misc and Web development.

If you need to sort varchar data as int, there is one very nice function in MySQL. Problem I had to solve was to sort values as below in a human logical way. Values were similar to this (and I wanted it to be sorted like below):

1; 2; 2a; 3; 4; 5; 5a; 5b; 5c; 6; 7; 11; 14; 14a; 16;

The problem was that if I put this data to MySQL database as varchar and sort by this column, MySQL returned it this way:

1; 11; 14; 14a; 16; 2; 2a; 3; 4; 5; 5a; 5b; 5c; 6; 7;

My second thought was to put is as INTEGER values… It would be sorted as it should but then I would loose values like

2a; 5a; 5b; 5c; 14a;

as it would be converted by MySQL to integer values (2; 5; 14;).

Then I have thought it would be best to put this data to database as a varchar values and sort it as int! After some googling I have found great function that have helped me! The solution was CAST function. To sort it the way I wanted I have just added to my MySQL query this sorting method (pic_number is my field with value to sort):

ORDER BY CAST(`pic_number` AS SIGNED)

and for reverse order

ORDER BY CAST(`pic_number` AS SIGNED) DESC

And it worked like a charm!

Function is as simple to use as:

CAST(expr AS TYPE)

Other possible conversion types you may need are:

  • BINARY[(N)]
  • CHAR[(N)]
  • DATE
  • DATETIME
  • DECIMAL[(M[,D])]
  • SIGNED [INTEGER]
  • TIME
  • UNSIGNED [INTEGER]

You may read more about this types in MySQL docs here. This function saved me a lot of coding in PHP. I hope it have helped you as much as it helped me!

Tags: , , .
11 Comments

11 Responses to “Sort VARCHAR as INT in MySQL query”

Feed for this Entry Trackback Address

Leave a Reply