MySQL CAST – How to Type Cast in MySQL

Last updated on August 24th, 2020 at 07:46 am

Sometimes you may need to cast MySQL data from one data type to another. Here’s how to typecast data using MySQL CAST function.

How to Type Cast in MySQL

We will look at how MySQL CAST works and how to typecast data using MySQL CAST function.

Here’s the syntax of MySQL CAST function

CAST(data as data_type)

MySQL CAST requires two inputs – the data to be typecasted and the data type (decimal, char, etc) to which you want to convert this data. You can cast data into BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED data types.

You can provide data as a literal value as shown below

CAST(1 as char)

OR

You can data as a column name (e.g id)

CAST(id as char)

Bonus Read : MySQL Rollback Query

Here’s a sample SQL query where we cast an int to a char in the SELECT clause

mysql> select cast(1 as char)
from sales;

You can also use MySQL CAST in WHERE clause. Here we convert string to int in WHERE clause.

mysql> select *
from sales
where id=CAST('213' as int);

Bonus Read : How to Use MySQL Rollup Query

MySQL CAST Examples

Let us look at some of the common examples of MySQL CAST function.

MySQL CAST as Int

Here’s an example to MySQL cast float to signed integer. Please note you can only cast to UNSIGNED or SIGNED integer data types. You cannot use INT in CAST function.

mysql> select cast(1.23 as signed);
+------------------------+
| cast(1.23 as   signed) |
+------------------------+
|                      1 |
+------------------------+

MySQL CAST as Decimal

Here’s how to cast as decimal

mysql> select cast(1.23 as decimal(4,3));
+----------------------------+
| cast(1.23 as decimal(4,3)) |
+----------------------------+
|                      1.230 |
+----------------------------+

Bonus Read : How to Check MySQL Version

MySQL CAST as Float

MySQL CAST does not support cast as float. You have to cast as decimal as shown above.

mysql> select cast(2.234 as decimal(5,3));
+-----------------------------+
| cast(2.234 as decimal(5,3)) |
+-----------------------------+
|                       2.234 |
+-----------------------------+

MySQL Cast as Varchar

MySQL CAST does not support cast as varchar. You need to cast as char as shown below.

mysql> select cast('xyz' as char);
+---------------------+
| cast('xyz' as char) |
+---------------------+
| xyz                 |
+---------------------+

MySQL Datetime to Date

Here’s how to cast datetime to date.

mysql> select cast('2020-01-01 13:30:00' as date);
+-------------------------------------+
| cast('2020-01-01 13:30:00' as date) |
+-------------------------------------+
| 2020-01-01                          |
+-------------------------------------+

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.