Introduction:

As a developer or database administrator working with MySQL, you will often encounter situations where data type conversions are essential. Whether you need to change the data type of a column or convert values during data manipulation, understanding the various conversion methods is crucial for smooth database operations. This blog will walk you through multiple techniques to convert data types in MySQL.

Method 1: Using CAST() Function

The CAST() function is a straightforward way to explicitly convert data from one data type to another in MySQL. It is especially handy for numeric or date/time conversions. Let's consider an example where we have a table named products with a price column stored as a decimal and we want to convert it to an integer:

SELECT product_name, CAST(price AS SIGNED) AS converted_price
FROM products;

Output:

+--------------+----------------+
| product_name | converted_price|
+--------------+----------------+
| Product 1    | 10             |
| Product 2    | 15             |
| Product 3    | 20             |
+--------------+----------------+

Method 2: Using CONVERT() Function

Similar to CAST(), the CONVERT() function allows data type conversions in MySQL. However, it offers additional flexibility, like converting character sets. Let's convert a string containing a date into a DATE data type:

SELECT CONVERT('2023-08-01', DATE) AS converted_date;

Output:

+----------------+
| converted_date |
+----------------+
| 2023-08-01     |
+----------------+

Method 3: Converting with Arithmetic Operations

Implicit data type conversions can be achieved through arithmetic operations. For instance, converting an INTEGER to a FLOAT:

SELECT some_integer_column / 1.0 AS converted_float
FROM my_table;

Output:

+----------------+
| converted_float|
+----------------+
| 2.5            |
| 3.0            |
| 1.75           |
+----------------+

Method 4: Using CASE Statements for Conditional Conversions

The CASE statement is excellent for condition-based conversions. Let's convert numeric grades in a column named grade to letter grades:

SELECT
    grade,
    CASE
        WHEN grade >= 90 THEN 'A'
        WHEN grade >= 80 THEN 'B'
        WHEN grade >= 70 THEN 'C'
        WHEN grade >= 60 THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM exam_results;

Output:

+-------+--------------+
| grade | letter_grade |
+-------+--------------+
| 85    | B            |
| 78    | C            |
| 92    | A            |
+-------+--------------+

Method 5: Converting with CONCAT() Function

The CONCAT() function can be used creatively to combine data from multiple columns. Let's create a DATE column by combining separate columns for year, month, and day:

SELECT CONCAT(year_column, '-', month_column, '-', day_column) AS converted_date
FROM my_table;

Output:

+----------------+
| converted_date |
+----------------+
| 2023-08-01     |
| 2023-08-02     |
| 2023-08-03     |
+----------------+

Method 6: Using FORMAT() Function for Display Formatting

The FORMAT() function allows you to convert numeric data into formatted strings with specified decimal places and thousand separators. Let's format the price column with two decimal places:

SELECT product_name, FORMAT(price, 2) AS formatted_price
FROM products;

Output:

+--------------+----------------+
| product_name | formatted_price|
+--------------+----------------+
| Product 1    | 10.00          |
| Product 2    | 15.50          |
| Product 3    | 20.75          |
+--------------+----------------+

Conclusion:

In this blog, we have explored various methods to convert data types in MySQL. The CAST() and CONVERT() functions offer explicit conversions, while arithmetic operations and CONCAT() provide implicit conversions. Additionally, the CASE statement enables condition-based conversions, and the FORMAT() function allows customized display formatting.