CONVERT
Syntax
CONVERT(expr,type), CONVERT(expr USING transcoding_name)
Contents
Description
The CONVERT() and CAST() functions take a value of one type and produce a value of another type.
The type can be one of the following values:
- BINARY[(N)]
- CHAR[(N)]
- DATE
- DATETIME[(D)] — Decimal (D) specifier new in 5.3
- DECIMAL[(M[,D])]
- DOUBLE[(M,D)] — New in 5.3
- INTEGER — New in 5.3
- Short for SIGNED INTEGER
- SIGNED [INTEGER]
- TIME[(D)] — Decimal (D) specifier new in 5.3
- UNSIGNED [INTEGER]
Note that in MariaDB, INT and INTEGER are the same thing.
BINARY produces a string with the BINARY data type. See BINARY for a description of how this affects comparisons. If the optional length N is given, BINARY(N) causes the cast to use no more than N bytes of the argument. Values shorter than N bytes are padded with 0x00 bytes to a length of N.
CHAR(N) causes the cast to use no more than N characters of the argument.
The main difference between the CAST() and CONVERT() is that CONVERT(expr,type) is ODBC syntax while CAST(expr as type) and CONVERT(... USING ...) are SQL92 syntax.
CONVERT() with USING is used to convert data between different character sets. In MariaDB, transcoding names are the same as the corresponding character set names. For example, this statement converts the string 'abc' in the default character set to the corresponding string in the utf8 character set:
SELECT CONVERT('abc' USING utf8);
Examples
SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);
Converting a BINARY to string to permit the LOWER function to work:
SET @x = 'AardVark'; SET @x = BINARY 'AardVark'; SELECT LOWER(@x),LOWER(CONVERT (@x USING latin1)); +-----------+----------------------------------+ | LOWER(@x) | LOWER(CONVERT (@x USING latin1)) | +-----------+----------------------------------+ | AardVark | aardvark | +-----------+----------------------------------+