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!
