How the CONVERT() function works in Mariadb?

The MariaDB CONVERT() function is used to convert a value from one data type to another. This function is handy when you need to handle values of different types and perform conversions between them, such as converting a string to a numerical value or changing character set encodings.

Syntax

The syntax for the MariaDB CONVERT() function is as follows:

CONVERT(value, type)

Where value is the value to convert, and type is the data type to convert the value to. The function returns the value converted to the specified type.

Examples

Example 1: Converting String to Integer

This example demonstrates converting a string into an integer.

SELECT CONVERT('123', SIGNED INTEGER);

The output for this statement is:

+--------------------------------+
| CONVERT('123', SIGNED INTEGER) |
+--------------------------------+
|                            123 |
+--------------------------------+

This shows the string ‘123’ converted to an integer 123.

Example 2: Converting String to Date

This example shows how to convert a string into a date.

SELECT CONVERT('2024-03-16', DATE);

The output for this statement is:

+-----------------------------+
| CONVERT('2024-03-16', DATE) |
+-----------------------------+
| 2024-03-16                  |
+-----------------------------+

The string ‘2024-03-16’ is successfully converted to a DATE type.

Example 3: Changing Character Set

Here we convert a string from one character set to another.

SELECT CONVERT('text' USING utf8mb4);

The output for this statement is:

+-------------------------------+
| CONVERT('text' USING utf8mb4) |
+-------------------------------+
| text                          |
+-------------------------------+

The string ’text’ remains the same, but its character set encoding is changed to utf8mb4.

Example 4: Converting Decimal to String

This example converts a decimal value to a string.

SELECT CONVERT(123.45, CHAR);

The output for this statement is:

+-----------------------+
| CONVERT(123.45, CHAR) |
+-----------------------+
| 123.45                |
+-----------------------+

The decimal 123.45 is converted to a string ‘123.45’.

Example 5: Attempting Invalid Conversion

This example attempts to convert a string to an integer where the string is not a valid number.

SELECT CONVERT('abc', SIGNED INTEGER);

The output for this statement is:

+--------------------------------+
| CONVERT('abc', SIGNED INTEGER) |
+--------------------------------+
|                              0 |
+--------------------------------+

Since ‘abc’ is not a valid number, the function returns 0.

Related Functions

Below are a few functions related to the MariaDB CONVERT() function:

• MariaDB CAST() function is used to explicitly convert a value from one type to another.
• MariaDB CHAR() function is used to convert a series of integer values to the corresponding string.
• MariaDB BINARY() function is used to convert a value to a binary string.

Conclusion

The CONVERT() function in MariaDB is a versatile tool for data type conversion, essential for data manipulation and transformation tasks. It allows for explicit conversions between different data types, ensuring data integrity and compatibility in operations. Always ensure that the conversions are valid to prevent unexpected results or errors.