Today i was working on a database which was more than 10 years old. My task is to fetch records form table and display based on sorting order. Here my problem is sorting column was defined as VARCHAR and values stored in are Integers.
Table: tbl_contact
In the above table i want to sort the records based on position field. As we know Integer sorting is not possible in varchar datatype. To solve this I read some articles from google and I found that Mysql provides an inbuilt function called CAST.
Syntax:
CAST makes my job easier. Here is my query that did the magic.
Example:
Output:
Table: tbl_contact
| Name(VARCHAR 20) | Position(VARCHAR 5) |
| Kathirason | 2 |
| Asokan | 3 |
| Techispeaks | 1 |
In the above table i want to sort the records based on position field. As we know Integer sorting is not possible in varchar datatype. To solve this I read some articles from google and I found that Mysql provides an inbuilt function called CAST.
Syntax:
CAST(expr AS TYPE)
CAST makes my job easier. Here is my query that did the magic.
Example:
SELECT * FROM `tbl_contact` ORDER BY CAST(`position` AS SIGNED) DESC
Output:
| Name | Position |
| Asokan | 3 |
| Kathirason | 2 |
| Techispeaks | 1 |
thanks for the info. i am searching for the same.
ReplyDelete