The CONVERT SQL Function in MySQL and MariaDB - Conversion function |
|
| | CONVERT | Syntax: | CONVERT(Expression [, in type] [, or character set]) | | Return value: | Depending on the data types used | | Function type: | Conversion function | |
| | The CONVERT() function converts data to other data types or strings to character sets.
1. Convert to data types.
Syntax: CONVERT(value, type):
The data "type" to convert to can contain the following values:
| BINARY | The length of the output can optionally be specified. |
| CHAR | The length of the character string and the charset can optionally be specified. |
| NCHAR | Like CHAR, but converts to the national character set. Optionally, the length of the character string can be specified. |
| VARCHAR | in Oracle mode, from MariaDB version 10.3. Not available in MySQL 8.0. |
| DATE | |
| DATETIME | Optionally, the number of digits for the output of microseconds can be specified here. The maximum value is 6. |
| TIME | |
| DECIMAL | Optional specification of value size and decimal places: DECIMAL(m[,d]) |
| DOUBLE | |
| FLOAT | Available in MariaDB from version 10.4.5. Present in MySQL 8.0. |
| INTEGER | Equivalent to SIGNED or SIGNED INTEGER. Not available in MySQL 8.0. |
| SIGNED | Or SIGNED INTEGER |
| UNSIGNED | Or UNSIGNED INTEGER |
If the value of "Expression" is invalid for conversion to a numeric value, 0 is returned.
Invalid characters are truncated when converting numeric values from strings.
A preceding invalid character results in the output of 0.
If the value of the argument is NULL, the function returns NULL.
2. Convert to character sets.
Syntax: CONVERT(value USING character set) or CONVERT(value, CHAR CHARACTER SET character set):
Numeric values are converted to strings.
The CHARSET() function can be used to display the character set of the result.
If the value of the argument is NULL, the function returns NULL. | Examples of converting to different data types |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
| SELECT convert(100, binary);
SELECT convert('Test', binary);
SELECT convert('Test', binary(2));
SELECT convert(100, char);
SELECT convert(100, char(2));
SELECT convert('Test', integer);
SELECT convert(100, double);
SELECT convert(100.11, integer);
SELECT convert(100.11, decimal);
SELECT convert(100.11, decimal(10,4));
SELECT convert(-100.11, unsigned);
SELECT convert('-100.11', unsigned integer);
SELECT convert('-100.11', signed);
SELECT convert('100.11Test', decimal(10,4));
SELECT convert('x100.11Test', decimal(10,4));
SELECT convert(' -100.11Test', signed);
SELECT convert('x100.11Test', signed);
select convert('2023-01-01', datetime);
select convert('2023-01-01', datetime(6));
select convert('2023-01-01 14:30:00', date);
select convert('2023-01-01 14:30:00', time);
select convert(null, char);
|
|
| convert(100, binary) |
|---|
| varchar(3) BINARY |
|---|
| 100 |
|
|
| convert('Test', binary) |
|---|
| varchar(4) BINARY |
|---|
| Test |
|
|
| convert('Test', binary(2)) |
|---|
| varchar(2) BINARY |
|---|
| Te |
|
|
| convert(100, char) |
|---|
| varchar(3) BINARY |
|---|
| 100 |
|
|
| convert(100, char(2)) |
|---|
| varchar(2) BINARY |
|---|
| 10 |
|
|
| convert('Test', integer) |
|---|
| int(4) |
|---|
| 0 |
|
|
| convert(100, double) |
|---|
| double(22) |
|---|
| 100 |
|
|
| convert(100.11, integer) |
|---|
| int(7) |
|---|
| 100 |
|
|
| convert(100.11, decimal) |
|---|
| decimal(11) |
|---|
| 100 |
|
|
| convert(100.11, decimal(10,4)) |
|---|
| decimal(12) |
|---|
| 100.1100 |
|
|
| convert(-100.11, unsigned) |
|---|
| bigint(20) |
|---|
| 0 |
|
|
| convert('-100.11', unsigned integer) |
|---|
| bigint(20) |
|---|
| 18446744073709551516 |
|
|
| convert('-100.11', signed) |
|---|
| int(7) |
|---|
| -100 |
|
|
| convert('100.11Test', decimal(10,4)) |
|---|
| decimal(12) |
|---|
| 100.1100 |
|
|
| convert('x100.11Test', decimal(10,4)) |
|---|
| decimal(12) |
|---|
| 0.0000 |
|
|
| convert(' -100.11Test', signed) |
|---|
| bigint(12) |
|---|
| -100 |
|
|
| convert('x100.11Test', signed) |
|---|
| bigint(11) |
|---|
| 0 |
|
|
| convert('2023-01-01', datetime) |
|---|
| datetime(19) |
|---|
| 2023-01-01 00:00:00 |
|
|
| convert('2023-01-01', datetime(6)) |
|---|
| datetime(26) |
|---|
| 2023-01-01 00:00:00.000000 |
|
|
| convert('2023-01-01 14:30:00', date) |
|---|
| date(10) |
|---|
| 2023-01-01 |
|
|
| convert('2023-01-01 14:30:00', time) |
|---|
| time(10) |
|---|
| 14:30:00 |
|
|
| convert(null, char) |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| Examples of converting to character sets |
1
2
3
4
5
6
7
8
9
10
11
12
13
|
| SELECT convert(100.233 USING utf8);
SELECT CHARSET(convert(100.234 USING utf8));
SELECT CHARSET(convert(100 USING utf8));
SELECT CHARSET(convert('Test' USING utf32));
SELECT CHARSET(convert('Test', char));
SELECT CHARSET(convert('Test', nchar)); /* To National Character Set */
SELECT CHARSET(convert('Test', char CHARACTER SET utf8mb4));
SELECT convert(null USING utf32);
|
|
| convert(100.233 USING utf8) |
|---|
| varchar(8) BINARY |
|---|
| 100.233 |
|
|
| CHARSET(convert(100.234 USING utf8)) |
|---|
| varchar(64) BINARY |
|---|
| utf8 |
|
|
| CHARSET(convert(100 USING utf8)) |
|---|
| varchar(64) BINARY |
|---|
| utf8 |
|
|
| CHARSET(convert('Test' USING utf32)) |
|---|
| varchar(64) BINARY |
|---|
| utf32 |
|
|
| CHARSET(convert('Test', char)) |
|---|
| varchar(64) BINARY |
|---|
| binary |
|
|
| CHARSET(convert('Test', nchar)) |
|---|
| varchar(64) BINARY |
|---|
| utf8 |
|
|
| CHARSET(convert('Test', char CHARACTER SET utf8mb4)) |
|---|
| varchar(64) BINARY |
|---|
| utf8mb4 |
|
|
| convert(null USING utf32) |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the CONVERT() function in MySQL and MariaDB databases | The CONVERT() function in MySQL and MariaDB is used to convert a value from a data type to another. It can be used to change the data type of a table column or a specific value within a query result and is therefore useful for various applications, such as data type conversions to handle data type conflicts and to perform calculations or comparisons for different data types, character set conversions to check compatibility between different character sets, numeric formatting to control precision, scaling, and formatting of decimal or floating point numbers, string manipulations to change the appearance or structure of string values in queries, and conditional value conversions to dynamically convert values based on certain conditions.
The behavior of the function may vary slightly between MySQL and MariaDB. | | Further MySQL und MariaDB SQL Conversion functions | |
| | | More information about the CONVERT SQL function: and and |
|
|
|
|
