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!
565 Followers
645 Subscribers
Subscribe via Facebook
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 ;)
how will it work If it is like Kathirason.02
Thank you for this post ! it resolved my problem
worked for me ,thanks
mine is
abc1
abc11
abc2,
and now i can sort them as
abc1
abc2
abc11
how abc1
abc11
abc2,
and now i can sort them as
abc1
abc2
abc11 you can did it plz share,,,,
The best answer for this question is
# tbl contains: 4a2d3,12a1b,1ac2 etc so on select * from tbl order by number_as_char * 1 asc, number_as_char asc expected output: 1ac2,4a2d3,12a1b#
worked for me ,thanks,thanks,thanks,thanks,thanks,thanks,thanks,thanks,thanks,thanks,thanks,thanks,thanks,thanks,thanks
I have varchar as
cr491
cr1003
cr8352
I have used
SELECT * FROM `widows` ORDER BY
cast(reverse(cast(reverse(substr(`ref`,2,4)) as signed)) as signed)
but it orders as 1003,491,8352 instead of
491,1003,8352 etc
How can I achieve numeric order of the ‘suffix’ after the cr
Eureka
SELECT * FROM `widows` ORDER BY lpad(rtrim(trim(leading ‘cr’ from ref)),4,’0′)
After a lot of trial and error
how can i sort this ?
8.55” x 6” x 4.75″
11” x 7” x 5″
10” x 6″ x 4.75″
9.75” x 6” x 4.75″
12” x 6.5” x 4.75″
Thank you :)
Thanks