I have a varchar field in my table and I want to sort it. But I need to handle this field as integer. Meaning if sort as text the order is "19,2,20" but I want to get the right order "2,19,20".
Can anyone help me?
Add a comment |
6 Answers
I somehow didn't manage to run the query with CAST. I was always getting Error Code: 1064 near "DECIMAL" (or other numeric type that I chose). So, I found another way to sort varchar as numbers:
SELECT * FROM mytable ORDER BY ABS(mycol) A bit simpler and works in my case.
Comments
SELECT * FROM mytable ORDER BY CAST(mycol AS DECIMAL) 
3 Comments
dnagirl
@Quassnoi: Just wondering, but is CASTing more efficient than ORDER BY mycol+0
Quassnoi
@dnagirl: DECIMAL will silently truncate everything beyond the [0-9] range, mycol + 0 (which implies CAST AS DOUBLE) will issue a warning. As for performance, I think they're identical.
d3wannabe
Abhi Beckert - Can't have a space between CAST and the opening bracket
Here is the solution
SELECT * FROM MyTable ORDER BY ABS(MyCol); 
Comments
All other answers use ABS, which converts the values into absolute (positive) values, assuming that the integers are positive. A better solution would be to use * 1:
SELECT * FROM mytable ORDER BY mycol * 1
This to prevent casting negative numbers into positive ones. Inspired by: mysql sort string number
Comments
You Can Order varchar field using this code according to your required
SELECT * FROM mytable ORDER BY ABS(mycol) 