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”

Field	Type	Null
id	int(11)	No
name	varchar(100)	No
ordernumber	varchar(100)	No

 

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

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

id	name	ordernumber
1	Jewel	404
2	Ahmed	420
3	Shabuj	001
4	Rose	First
5	Janina	100
6	HaJani	112
7	Test	Start
8	NameValue	121
9	String	021
10	Rahim	1
11	Korim	4

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 ;

id	name	ordernumber
3	Shabuj	001
9	String	021
10	Rahim	1
5	Janina	100
6	HaJani	112
8	NameValue	121
11	Korim	4
1	Jewel	404
2	Ahmed	420
4	Rose	First
7	Test	Start

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 ..

 

id	name	ordernumber
4	Rose	First
7	Test	Start
10	Rahim	1
3	Shabuj	001
11	Korim	4
9	String	021
5	Janina	100
6	HaJani	112
8	NameValue	121
1	Jewel	404
2	Ahmed	420

 

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:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER]

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