Sort VARCHAR as INT in MySQL query

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!