mySql Type Casting

Some times in SQL query you need to change the data type of table field with keeping the table structure as it is. Some thing like Integer to string, string to integer, string to date etc…. This type of data type conversion is known as data type casting. For this operation in SQL you can use mysql CAST function.
The CAST() function takes a value of one type and produce a value of another type.
What is Type Custing?
Converting an expression of a given type into another type is known as type– casting.

Mysql CAST() :
Suppose in your database you have a table with this structure
Tablename : “TestTable”

And in this table you have some of value like this…

SQL query: SELECT * FROM `TestTable` LIMIT 0, 30 ;

When you try for a Recordset with ordernumber ASC Then tha result will shown like this….

SQL query: SELECT * FROM `TestTable` ORDER BY `ordernumber` ASC LIMIT 0, 30 ;

In this RecordSet when the Mysql query are executed it returned a sorted RecordSet with ordernumber ascending. But you want a sorted RecordSet with proper numerical order of ordername field. For which you can find a RecordSet as ..

You have to write this sql
SQL query: SELECT * FROM `TestTable` ORDER BY CAST(ordernumber AS UNSIGNED INTEGER ) ASC LIMIT 0 , 30 ;
Mysql CAST() Syntex :
CAST(expression as type)

The type can be one of the following values:

More Example :
If a field with datatype VARCHAR(100) and also have a value “0420”, For normal selection of data you got a value 0420 which is a string. But you want a integer value that is 420.
You can write a sql like
SQL query: SELECT CAST(fld_name AS UNSIGNED INTEGER) as fld_name FROM tbl_name LIMIT 0,1;
Resulted Record will : 420 not 0420
For more details you can visit :
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast