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

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

4 thoughts on “mySql Type Casting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: