How the CONVERT() function works in Mariadb?

The CONVERT() function is a string function that converts a value from one character set to another character set. The function returns a string that represents the converted value in the new character set.

Posted on

The CONVERT() function is a string function that converts a value from one character set to another character set. The function returns a string that represents the converted value in the new character set. The function can handle different types of values, such as strings, numbers, dates, and times.

Syntax

The syntax of the CONVERT() function is as follows:

CONVERT(value, charset)

The function takes two arguments, value and charset, which are the value to be converted and the name of the character set to convert to, respectively. The arguments can be strings or expressions that evaluate to strings. The function returns NULL if any argument is NULL or invalid.

Examples

Example 1: Converting a string from utf8 to latin1

The following example uses the CONVERT() function to convert a string from utf8 to latin1. The string contains some Chinese characters.

SELECT CONVERT('你好,世界', 'latin1');

The output is:

+-----------------------------+
| CONVERT('你好,世界', 'latin1') |
+-----------------------------+
| ??,????                     |
+-----------------------------+

The output shows that the string is converted to latin1, but the Chinese characters are lost, as they are not supported by the latin1 character set.

Example 2: Converting a string from latin1 to utf8

The following example uses the CONVERT() function to convert a string from latin1 to utf8. The string contains some accented characters.

SELECT CONVERT('Bonjour, le monde', 'utf8');

The output is:

+---------------------------------+
| CONVERT('Bonjour, le monde', 'utf8') |
+---------------------------------+
| Bonjour, le monde               |
+---------------------------------+

The output shows that the string is converted to utf8, and the accented characters are preserved, as they are supported by the utf8 character set.

Example 3: Converting a number from utf8 to latin1

The following example uses the CONVERT() function to convert a number from utf8 to latin1. The number is represented as a string.

SELECT CONVERT('42', 'latin1');

The output is:

+----------------------+
| CONVERT('42', 'latin1') |
+----------------------+
| 42                   |
+----------------------+

The output shows that the number is converted to latin1, and the value is unchanged, as the digits are supported by both character sets.

Example 4: Converting a date from utf8 to latin1

The following example uses the CONVERT() function to convert a date from utf8 to latin1. The date is represented as a string.

SELECT CONVERT('2023-01-01', 'latin1');

The output is:

+---------------------------+
| CONVERT('2023-01-01', 'latin1') |
+---------------------------+
| 2023-01-01                |
+---------------------------+

The output shows that the date is converted to latin1, and the value is unchanged, as the hyphens and the digits are supported by both character sets.

Example 5: Converting a value with an invalid character set

The following example uses the CONVERT() function to convert a value with an invalid character set. The invalid character set is represented by ‘Foo’.

SELECT CONVERT('Hello, world', 'Foo');

The output is:

+---------------------------+
| CONVERT('Hello, world', 'Foo') |
+---------------------------+
| NULL                      |
+---------------------------+

The output shows that the function returns NULL, as expected. The function returns NULL if the character set argument is not a valid name.

There are some other functions that are related to the CONVERT() function in Mariadb. They are:

  • CAST(): This function converts a value from one data type to another data type. It returns a value that represents the converted value in the new data type. The function can handle different types of values, such as strings, numbers, dates, and times.
  • COLLATE(): This function converts a string from one collation to another collation. It returns a string that represents the converted value in the new collation. The function can handle different types of strings, such as utf8, latin1, and binary.
  • CHARSET(): This function returns the name of the character set of a value. It can handle different types of values, such as strings, numbers, dates, and times.
  • COLLATION(): This function returns the name of the collation of a string. It can handle different types of strings, such as utf8, latin1, and binary.

Conclusion

The CONVERT() function is a useful function to convert a value from one character set to another character set. It can handle different types of values, such as strings, numbers, dates, and times. It returns a string that represents the converted value in the new character set. It is similar to the CAST() function, but with more flexibility. It is also related to some other functions that provide character set or collation information or conversion.