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!
781 Followers
1104 Fans
145 Subscribers
409 Members
Add me on Google+
Subscribe via eMail
sweet! Thanks very much
very usefull,Thanks a lot
I have been looking for this. Could not figure it out myself. Thank you very much for sharing!
Awesome. Just what i needed
This saved me a load of time rewriting a predecessors’ code!
You saved my day.
You sir, are a genius.
Thank You
thanks a lot…i tried it and it works
Thank you! helped me a lot.
Type casting for the win. Thanks!
Thank you!!! quick google search landed me here… problem solved within a few min because of this post!!!
thanz
Another way I found to do it was this way,
ORDER BY (0+var_char_field)
which forces it to evaluate a varchar field as a number.
Any advice comparing these two methods? Any problems with either method?
Thanks
it worked like a charm!
Hi Thanks for a wonderful solution. But when I am trying to sort values in following order like: 10.50, 10.00, 10.00, 9.50, 9.00 this solution breaks and provides data in below order:
10.00, 10.00, 10.50 and so on. Is there any way by which I can also sort in decimal places?
just use DECIMAL instead of SIGNED?? Maybe also change jobs?
Thanks dude its helped me a lot.
Not working
Table: tbl_contact
Name(VARCHAR 20) Position(VARCHAR 5)
Kathirason 02
Asokan 03
Techispeaks 01
Pro 100
Ken 50
Output:
Name(VARCHAR 20) Position(VARCHAR 5)
Kathirason 01
Asokan 02
Techispeaks 03
Pro 100
Ken 50
Tony, i’ve got something which is working where numeric value is at the end of the expression.
Example :
cast(reverse(cast(reverse(“Kathirason 02″) as signed)) as signed).
You can put this mixed functions call in the ORDER BY, replacing “Kathirason 02″ by the name of the column you want to sort.
1) It reverses the expression => 20 Kathirason
2) It casts it as signed value => 20
3) It reverses it again to get the original value => 02
4) It casts it as signed to make a numeric sort. => 2
I hope it would be useful for somebody ;)