Eliminating Wrong Data Type Issue Using MySQL Typecasting

No votes yet.
Please wait...

Hey Mates,

We are here with the fix to the most recent problem we faced while working on a CRM i.e TYPE CASTING Problem.

For those who are not aware what, TYPE CASTING is let us suppose a scenario where you have by mistake or intentionally used a Varchar Data type field for what supposed to be the Integer/Decimal field.

Here the most basic problem occurs while querying the data by using ORDER BY clause is that it orders all the data of that particular field by 1st character for example.

Let us suppose a situation where we want to obtain all the ages in an ascending order from the table below whose AGE field is a Varchar which was supposed to be Integer.

Table Name – Users

 

ID Name Age (This field has varchar datatype)
1 Deepak Shah 25
2 Manglesh Upadhyay 24
4 Saurabh Dubey 35
5 Manjusha Mishra 42
6 Jyotika Gupta 55
7 Akash Kumar 45
8 Harsh Rajput 11
9 Pravesh Singh 17
10 Parteek Kumar 32

Now for getting all users age you will have a query

1
$this->db->select(‘Age’)->from(‘users’)->order_by(‘Age’,’ASC’);

Now the above query will give you the most frustrating result like.

Output-

19
11
17
25
24
35
32
42
45
55

Here Since the field datatype is Varchar so the MySQL is ordering the Age by using 1st character and first come first serve basis i.e 19 came ahead of 17 so 19 will be ordered first.

Now in order to tackle the situation you don’t need to change the data type of the field as you might end up losing your precious data. Now Typecasting will act as a life saver for you for above query to work correctly you will have to use CAST Function in order by clause. Take a look

Now the code will be

1
$this->db->select(‘Age’)->from(‘users’)->order_by('CAST(Age AS DECIMAL(10,6)) ASC');

This will simply tell MYSQL to CAST i.e to change the field from Varchar to Decimal for this particular query and then order it in ascending order.

The above Query will yield the output-

11
17
19
24
25
32
35
42
45
55

Hurray! Hope this tutorial will help you in fixing the Type Casting or Wrong selection of data type issue for our newbie’s who have started their careers and are unaware of these horrendous effects of choosing the wrong data type.

Stay Tuned for next Quick fix.

Because we believe Hurdles aren’t really Hurdles

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
18 − 8 =