MySQL CONVERT

The MySQL CONVERT function is used to convert a value from one data type to another. It is particularly useful when you need to manipulate or present data in a different format or type.

The primary use of the CONVERT function is to convert data types within MySQL statements. It allows you to change the representation of data without altering its underlying value. For instance, you can convert a numerical value from an integer to a floating-point number or a string to a date format.

Syntax

The general syntax of the CONVERT function is as follows:

CONVERT(expr, type)

Here, expr is the expression or value that you want to convert, and type is the target data type to which you want to convert the expression.

Example

Let’s consider a simple example where you have a column named price with values stored as strings, and you want to convert them to decimal numbers. The query might look like this:

SELECT product_name, 
CONVERT(price, DECIMAL(10,2)) AS converted_price
FROM products;

In this example, price is the column containing the values you want to convert, and DECIMAL(10,2) specifies the target data type as a decimal number with a total of 10 digits, with 2 digits to the right of the decimal point.

Converting a Numerical Value:

SELECT CONVERT(12.34, DECIMAL(10,2));

Result: 12.34

Converting a Character String:

SELECT CONVERT('Hello, World!', CHAR);

Result: Hello, World!

Converting a Date and Time:

SELECT CONVERT('2024-01-02 12:34:56', DATETIME);

Result: 2024-01-02 12:34:56

Convert ASCII to UTF-8:

SELECT CONVERT('Hello, World!' USING utf8mb4);

Additional notes

The CONVERT function is often used in conjunction with other functions to achieve more complex data manipulations.

It’s important to note that the exact syntax and available data types might vary depending on the MySQL version you are using.

In addition to the generic CONVERT function, MySQL also supports specific conversion functions like CAST and CONVERT_TZ for particular scenarios.

The CONVERT function is a valuable tool for handling data conversions in MySQL. It simplifies data manipulation and ensures compatibility across different character sets. By understanding its syntax and usage, you can enhance the accuracy and efficiency of your MySQL queries.