MariaDB CONVERT() Function

In MariaDB, it CONVERT() is a built-in function that converts any type of parameter value to the specified type or character set.

MariaDB CONVERT() Syntax

Here is the syntax of the MariaDB CONVERT() function:

CONVERT(expr, data_type)

or

CONVERT(expr USING charset)

CONVERT(expr, data_type) is equivalent to CONVERT(expr, data_type).

Parameters

expr

Optional. A value whose data type needs to be converted.

data_type

Optional. The target data type. You can use the following types:

  • BINARY
  • CHAR
  • DATE
  • DATETIME
  • DECIMAL[(M[,D])]
  • DOUBLE
  • FLOAT
  • INTEGER
  • SIGNED [INTEGER]
  • UNSIGNED [INTEGER]
  • TIME
  • VARCHAR
charset

Optional. The character set to convert to.

Return value

The MariaDB CONVERT() function converts any type of parameter value to the specified type or character set and returns it.

MariaDB CONVERT() Examples

Here are some common examples of the MariaDB CONVERT() function.

Convert to binary

You can use the MariaDB CONVERT() function to convert a string to a binary string.

SELECT CONVERT('hello', BINARY);

+--------------------------+
| CONVERT('hello', BINARY) |
+--------------------------+
| hello                    |
+--------------------------+

Note that in the mysql client, binary strings are printed in hexadecimal by default.

You can also use the BINARY operator to get the binary string of a string, as follows:

SELECT BINARY 'hello';

+----------------+
| BINARY 'hello' |
+----------------+
| hello          |
+----------------+

Convert to integer

You can use the CONVERT() function to convert a floating point number to an integer.

SELECT
  CONVERT(1.23, INT),
  CONVERT('1.23', INT),
  CONVERT('1.23A', INT);

Output:

+--------------------+----------------------+-----------------------+
| CONVERT(1.23, INT) | CONVERT('1.23', INT) | CONVERT('1.23A', INT) |
+--------------------+----------------------+-----------------------+
|                  1 |                    1 |                     1 |
+--------------------+----------------------+-----------------------+

This function will return 0 if MariaDB cannot convert the given value to an integer.

SELECT CONVERT('abc', INT);

Output:

+---------------------+
| CONVERT('abc', INT) |
+---------------------+
|                   0 |
+---------------------+

Convert to datetime

You can use the CONVERT() function to convert a date/time value represented by a string to data of DATE or DATETIME type.

SELECT
    CONVERT('2023-02-28', DATE),
    CONVERT('10:10:10', TIME),
    CONVERT('2023-02-28 10:10:10', DATETIME);

+-----------------------------+---------------------------+------------------------------------------+
| CONVERT('2023-02-28', DATE) | CONVERT('10:10:10', TIME) | CONVERT('2023-02-28 10:10:10', DATETIME) |
+-----------------------------+---------------------------+------------------------------------------+
| 2023-02-28                  | 10:10:10                  | 2023-02-28 10:10:10                      |
+-----------------------------+---------------------------+------------------------------------------+

This function will return NULL if MariaDB cannot convert the given value to a date/time type.

SELECT CONVERT('ABC', DATE);

Output:

+----------------------+
| CONVERT('ABC', DATE) |
+----------------------+
| NULL                 |
+----------------------+

Convert character set

This example changes the charset of Hello to the latin1 charset:

SELECT CONVERT("Hello" USING latin1);

+-------------------------------+
| CONVERT("Hello" USING latin1) |
+-------------------------------+
| Hello                         |
+-------------------------------+

Garbled characters will appear if the given character set used cannot represent the corresponding string. for example:

SELECT
    CONVERT("好" USING latin1),
    CONVERT("好" USING utf8mb4);

+-----------------------------+------------------------------+
| CONVERT("好" USING latin1)  | CONVERT("好" USING utf8mb4)  |
+-----------------------------+------------------------------+
| ?                           | 好                           |
+-----------------------------+------------------------------+

Here, because the character 好 exceeds the range of latin1, the output includes garbled characters. And the output of utf8mb4 is good.

Summarize

In MariaDB, CONVERT() is a built-in function that converts any type of parameter value to the specified type or character set.