MySQL Convert Function

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!