MySQL CONVERT() Function

As the name suggests, MySQL CONVERT() function converts the value from one data type to another. It’s that simple! Let’s suppose you have a float value in your program, which is generated somehow, and you want to store it in the database as an integer after rounding it. In such a case, you can use CONVERT() function to get your work done!

Have you ever tried inserting an int type data into the char type column in MySQL? If yes, you might have realized that MySQL implicitly converts the data type of value before inserting it into the database. But, you should not always rely on implicit conversion of the data because, in some cases, you might face errors and will end up inserting false/wrong values. That’s where MySQL CONVERT() function comes into action!

Note that you don’t need to use the MySQL CONVERT() function all the time to change the data type of values because generally, implicit conversion takes place for converting lower data types into higher data types.

Let’s see the syntax of the CONVERT() function with some examples.

Also read: The MySQL CAST() Function

MySQL CONVERT() Function Syntax

CONVERT() function is available in many databases; however, its syntax is slightly different in all of them. Following is the basic syntax for the CONVERT() function.

CONVERT(expression, type);Code language: SQL (Structured Query Language) (sql)

For example, convert the string number into a float number.

SELECT CONVERT("12.23", FLOAT);Code language: SQL (Structured Query Language) (sql)

Using the CONVERT() function, you can also convert the data between different character sets! Following is the standard SQL syntax to convert the expression into a specific character set.

CONVERT(expression USING transcoding_name);Code language: SQL (Structured Query Language) (sql)

Here, expression is your value, and transcoding_name is your character set name like utf8mb4, utf8mb3, utf8, utf16, etc. Don’t confuse between transcoding_name and character set name; both are the same! Some data type also allows you to pass an optional parameter to control the output. Let’s see some examples!

Examples Of MySQL CONVERT() Function

Though the CONVERT() function allows you to convert the value into a specified data type, the number of data types is limited to convert the value into. You can convert the data into one of the following data types: DATE, DATETIME, TIME, DECIMAL, CHAR, BINARY, DOUBLE, FLOAT, SIGNED, UNSIGNED, YEAR.

We will see a few data types conversion here, which will help you understand the usage of the CONVERT() function. You can check MySQL official documentation for the implementation of every data type.

Let’s start with the DECIMAL data type. As the name suggests, DECIMAL produces a decimal value and has two optional parameters; M and D, where M is the maximum number of digits, also known as precision, and D is a number of digits after decimal point known as scale.

The basic syntax for it is:

CONVERT(exp, DECIMAL[(M,[,D])])Code language: SQL (Structured Query Language) (sql)

The square bracket describes- everything inside the brackets is optional. Here are some examples of DECIMAL.

SELECT CONVERT(20.23, DECIMAL(3,1));Code language: SQL (Structured Query Language) (sql)

As you can see, the output contains three digits in total and a single digit after the decimal point as specified in the parameters to the decimal data type. You can also choose not to pass any parameter to the DECIMAL data type, which will eventually give you output as an integer value. Check the example below.

SELECT CONVERT(20.23, DECIMAL);Code language: SQL (Structured Query Language) (sql)

Note, if you are not passing any parameter to the data type, you must write the name without parenthesis, or you will get an error.

Now, We will check the functionality of the CONVERT() function with date and time values so that all your doubts will be clear. Let’s convert the string date-time into date-time format first.

Note that we have written the standard date-time format below as a string so, the output will also be the same.

SELECT CONVERT("2021-12-15 12:20:10", DATETIME) AS DateTime;Code language: SQL (Structured Query Language) (sql)

Here, the first parameter is the string date-time value, ‘DATETIME’ is a datatype and ‘DateTime’ is an alias for the output column.

We’re receiving the exact same result as an input parameter. So, what exactly did the CONVERT() function perform in this case? To understand it, look at another example that will clear your doubt.

SELECT CONVERT("2021/12/15 12:20:10", DATETIME) AS DateTime;Code language: SQL (Structured Query Language) (sql)

You can see, we have written the date-time parameter in a different style, but the CONVERT() function casts this value into a valid DateTime format.

Similarly, you can also split the day, month, and year by the dot(.), and still, you will get an output in the valid MySQL DateTime format.

Now, what if you have a DateTime value inside your table, but you want only the date value to show on the output page? You can achieve this by using the CONVERT() function with DATE datatype. Check out the below example.

SELECT CONVERT("2021/12/15 12:20:10", DATE) AS Date;Code language: SQL (Structured Query Language) (sql)

You can see in the output, we have obtained the date from the parameter of DateTime format. Similarly, you can obtain only time too, using TIME datatype.

SELECT CONVERT("2021/12/15 12:20:10", TIME) AS Time;Code language: PHP (php)

That’s enough for the DATETIME data type; we will now see examples of BINARY data type in the CONVERT() function to get a clearer idea of how you can implement different data types of CONVERT() function in your query or statement.

The CONVERT() function with BINARY data type will convert a value into a binary string. It is useful when you want a strict comparison of two values or strings. Here, the strict indicates a byte by byte comparison. Didn’t understand the byte-by-byte comparison concept? Let’s see an example to understand it better.

SELECT "HELLO" = "hello";Code language: SQL (Structured Query Language) (sql)

Here, we have compared two strings. Even though both strings look similar, they are different by the letter casing. MySQL will compare two strings on a character-by-character basis, and it concludes both strings are identical; therefore, it returns 1.

What if we want to return true only if both strings are identical and of the same letter case? We can use BINARY to accomplish this.

SELECT CONVERT("HELLO",BINARY)= "hello";Code language: SQL (Structured Query Language) (sql)

Here, “HELLO” will be converted into a binary string, and then the byte-by-byte comparison will occur; therefore, it returns 0, unlike the previous example.

We can also convert an expression into a specified character set using the USING clause with a character set name. However, you will rarely use these in your project.

SELECT CONVERT('test' USING utf8mb4);Code language: SQL (Structured Query Language) (sql)

Conclusion

This is all for MySQL CONVERT() function. We have studied and understood what CONVERT() function does, how it works, its parameters, and examples. You can try converting the value in every valid datatype using the CONVERT() function. MySQL also allows you to convert the values using the CAST() function, which you can check from here. (cast() article link) Check out the MySQL official documentation from the below link for more information.

References

MySQL official documentation for CAST() and CONVERT() function.