Sometimes you may need to convert data in MySQL or type cast data in MySQL. Here’s how to convert data from one data type to another using MySQL CONVERT function. It can also be used to convert data from one character set to another.
How to Convert Data in MySQL
Here are the steps to convert data in MySQL using MySQL CONVERT function. Here’s the syntax of MySQL CONVERT function
CONVERT(input_value, data_type)
In the above function, you need to provide input_value as literal string or column name, and data_type to which you want to convert this data. Like MySQL CAST, you can cast data into BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED data types.
For example, here’s how to convert literal value to another data type
CONVERT(1, char)
and here’s how to convert a column (e.g id) to another data type
CONVERT(id, char)
Here’s MySQL CONVERT syntax to convert character sets
CONVERT(input_value USING character_set)
In the above function, you need to provide input_value as literal string or column name, and character_set to which you want to convert this data.
Here’s an example of how to cast as latin1
mysql> select convert('test string' using 'latin1');
+---------------------------------------+
| convert('test string' using 'latin1') |
+---------------------------------------+
| test string |
+---------------------------------------+
Bonus Read : How to Type Cast in MySQL
MySQL CONVERT Examples
Let us look at some examples of MySQL CONVERT function.
MySQL CONVERT String to Date
Here’s how to convert string to date
mysql> select convert('2020-08-01',date);
+----------------------------+
| convert('2020-08-01',date) |
+----------------------------+
| 2020-08-01 |
+----------------------------+
Bonus Read : MySQL Rollback Query
MySQL CONVERT String to Decimal
Here’s how to convert string to decimal
mysql> select convert('1.234',decimal(4,3));
+-------------------------------+
| convert('1.234',decimal(4,3)) |
+-------------------------------+
| 1.234 |
+-------------------------------+
Bonus Read : How to Use MySQL Rollup
MySQL CONVERT String to Double
MySQL does not support conversion to double data type. You will need to convert string to decimal.
mysql> select convert('2.134',decimal(4,3));
+-------------------------------+
| convert('2.134',decimal(4,3)) |
+-------------------------------+
| 2.134 |
+-------------------------------+
MySQL CONVERT String to Float
Again, MySQL does not support conversion to floating point data type. You will need to convert string to decimal.
mysql> select convert('2.134',decimal(4,3));
+-------------------------------+
| convert('2.134',decimal(4,3)) |
+-------------------------------+
| 2.134 |
+-------------------------------+
Bonus Read : How to Disable Foreign Key Check in MySQL
MySQL CONVERT String to int
Here’s an example to MySQL convert string 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('123' as signed);
+------------------------+
| cast('123' as signed) |
+------------------------+
| 1 |
+------------------------+
MySQL CONVERT Datetime to date
Here’s how to convert datetime to date. You can also use it to convert timestamp to date.
mysql> select convert('2020-08-01 13:00:00',date);
+-------------------------------------+
| convert('2020-08-01 13:00:00',date) |
+-------------------------------------+
| 2020-08-01 |
+-------------------------------------+
Bonus Read : How to Check MySQL Version
MySQL CONVERT Blob to Text
MySQL CONVERT does not support conversion to text data type. You need to convert blog to char. Here’s a sample to convert small blob into char.
mysql> select convert('wewewwbwbddwg',char);
+-------------------------------+
| convert('wewewwbwbddwg',char) |
+-------------------------------+
| wewewwbwbddwg |
+-------------------------------+
Hopefully, now you can easily convert data in MySQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
