How to Use MySQL Conversion Functions

Using Conversion Functions

MySQL Conversion Functions convert a value from one data type to another. Conversions can be conducted between string, date, and numeric type of data.

There are three Conversion Functions in MySQL: CONVERT, CAST, BINARY.

Function	Description
CONVERT	CONVERT can be used in either of the following two forms: Form 1: CONVERT(expr,type) In this form, CONVERT takes a value in the form of expr and converts it to a value of type. The type can be one of the following values: BINARY[(N)] Converts a value to BINARY data type. After a value is converted to BINARY type, comparisons are conducted byte by byte rather than character by character. This causes the comparison to be case sensitive even if the column isn't defined as BINARY or BLOB. BINARY also causes trailing spaces to be significant. See Practice #1-1. CHAR[(N)] Converts a value to character type of data. CHAR(N) causes the convert to use no more than N characters of the argument. See Practice #1-2. DATE Converts a value to DATE data type in the format of YYYY-MM-DD. See Practice #1-3. DATETIME Converts a value to DATETIME data type in the format of YYYY-MM-DD HH:MM:SS. See Practice #1-4. DECIMAL[(M[,D])] Converts a value to DECIMAL data type. The optional arguments M and D specify the precision (M specifies the total number of digits) and the scale (D specifies the number of digits after the decimal point) of the decimal value. The default precision is two digits after the decimal point. See Practice #1-5. SIGNED [INTEGER] Converts an unsigned integer to a signed integer. For an example of a practical use, read Tip #2 in How to enforce data type constraint article. TIME Converts a signed integer to an unsigned integer. See Practice #1-7. UNSIGNED [INTEGER] Converts a signed integer to an unsigned integer. See Practice #1-8. Form 2: CONVERT(expr USING transcoding_name) In this form, CONVERT() with USING is used to convert data between different character sets (utf8, latin1, ...) In MySQL, transcoding names are the same as the corresponding character set names. See Practice #1-9.
CAST	CAST(expr AS type) Using CAST() function is the same as using CONVERT() function except that it uses keyword AS in between expr and type rather than a comma. See Practice #1-10 and Practice #1-11.
BINARY	BINARY str BINARY is an operator rather than function. It casts a string following it to a binary string. BINARY str is shorthand for CAST(str AS BINARY) or CONVERT(expr, BINARY) This is an easy way to force a column comparison to be done byte by byte rather than character by character. This causes the comparison to be case sensitive even if the column isn't defined as BINARY or BLOB. BINARY also causes trailing spaces to be significant. See Practice #1-12.

Practice #1-1: Using CONVERT function to convert varchar stype to binary type.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
Binary type forces the comparison to be done byte by byte 
rather than character by character. Also it causes the comparison 
to be case sensitive even if the column is defined VARCHAR rather 
than as BINARY or BLOB. BINARY also causes trailing spaces to be 
significant. 
 
In the two queries below, CustomerID is converted to BINARY 
before the comparison is conducted. CustomerID is a varchar 
type of column but after the conversion, it becomes binary type.
*/
 
-- Query 1:
-- Zero rows are returned from the query because ALFAA is not 
-- equal to ALFAa in binary format.
SELECT CustomerID
FROM orders
WHERE CONVERT(CustomerID, BINARY)='ALFAa';
 
-- Query 2:
-- Zero rows are returned from the query because ALFAA is not 
-- equal to 'ALFAA ' in binary format because 'ALFAA ' has a 
-- trailing space.
SELECT CustomerID
FROM orders
WHERE CONVERT(CustomerID, BINARY)='ALFAA ';

Practice #1-2: Using CONVERT function to convert date to string.

/*
In this query, we only need info for the year, month, and date part 
of the OrderDate column. CONVERT is used to convert OrderDate to 
a string type of data and limit it to no more than 10 characters.
*/
SELECT CONCAT(CustomerID, ' ordered on ', CONVERT(OrderDate, CHAR(10)))
FROM orders;

Query result set - 830 rows returned:
Using CONVERT function to convert date to string.

Practice #1-3: Using CONVERT function to convert string to date type.

/*
This query converts a string to a DATE data type.
The result is always in YYYY-MM-DD format.
*/
SELECT CONVERT('1998-03-16 00:00:00', DATE) AS date1,
       CONVERT('98-03-16 00:00:00', DATE) AS date2,
       CONVERT('19980316', DATE) AS date3,
       CONVERT('980316', DATE) AS date4;

Query result set - 1 row returned:
Using CONVERT function to convert string to date type.

Practice #1-4: Using CONVERT function to convert string to DATETIME type.

/*
This query converts a string to a DATETIME data type.
The result is always in YYYY-MM-DD HH:MM:SS format.
*/
SELECT CONVERT('1998-03-16', DATETIME) AS datetime1,
       CONVERT('98-03-16', DATETIME) AS datetime2,
       CONVERT('19980316', DATETIME) AS datetime3,
       CONVERT('980316', DATETIME) AS datetime4;

Query result set - 1 row returned:
Using CONVERT function to convert string to DATETIME type.

Practice #1-5: Using CONVERT function to convert double to decimal type.

/*
This query converts UnitPrice column from 
double data type to decimal data type.
*/
SELECT ProductName, CONVERT(UnitPrice, DECIMAL)
FROM products;

Query result set - 77 rows returned:
Using CONVERT function to convert double to decimal type.

Practice #1-7: Using CONVERT function to convert string to time data type.

/*
This query converts a string to a TIME data type.
The result is always in HH:MM:SS format.
*/
SELECT CONVERT('1998-03-16 21:03:16', TIME) AS time1,
       CONVERT('210316', TIME) AS time2;

Query result set - 1 row returned:
Using CONVERT function to convert string to time data type.

Practice #1-8: Using CONVERT function to convert an integer to unsigned integer data type.

/*
If you are using numeric operators (such as + or -) and one 
of the operands is an unsigned integer, the result is unsigned.
 
This query converts an unsigned integer (the result of 0-5) 
to unsigned integer. Because the unsigned 64-bit big int 
ranges from 0 to 18446744073709551615, the result is 
converted to unsigned value 18446744073709551611, which is
18446744073709551616 - 5 = 18446744073709551611
 
18446744073709551616 is used because the range starts with 0.
 
In the second part of the SELECT statement, the result is
converted to signed integer so the correct result is displayed.
*/
SELECT CONVERT(0-5, UNSIGNED), 
       CONVERT(CONVERT(0-5, UNSIGNED), SIGNED);

Query result set - 1 row returned:
Using CONVERT function to convert an integer to unsigned integer data type.

Practice #1-9: Using CONVERT function with USING keyword.

/*
This query converts the string 'abc' in the default character 
set to the corresponding string in the utf8 character set.
*/
SELECT CONVERT('abc' USING utf8);

Query result set - 1 row returned:
Using CONVERT function with USING keyword.

Practice #1-10: Using CAST function to display internal numeric value for an enum column.

/*
This query reveals what the internal numeric value 
is for an enum column. It displays that y is internally
represented by 1 and n is internally represented by 2.
 
It also demonstrates that ORDER BY enum column is actual
order the column by internal numeric value.
*/
SELECT ProductName, 
       Discontinued AS enum,
       CAST(Discontinued AS SIGNED) AS enum_as_int
FROM products
ORDER BY Discontinued;

Query result set - 77 rows returned:
Using CAST function to display internal numeric value for an enum column.

Practice #1-11: Using CAST function in ORDER BY clause.

/*
This query shows that when converting an enum 
column to a string in ORDER BY clause, the
result is ordered by lexical sort rather than enum
column's internal numeric representation.
*/
SELECT ProductName, 
       CAST(Discontinued AS CHAR) AS enum_as_char
FROM products
ORDER BY CAST(Discontinued AS CHAR) DESC;

Query result set - 77 rows returned:
Using CAST function in ORDER BY clause.

Practice #1-12: Using BINARY operator to convert varchar stype to binary type.

/*
Binary type forces the comparison to be done byte by byte 
rather than character by character. Also it causes the comparison 
to be case sensitive even if the column is defined VARCHAR rather 
than as BINARY or BLOB. BINARY also causes trailing spaces to be 
significant. 
 
In the two queries below, CustomerID is converted to BINARY 
before the comparison is conducted. CustomerID is a varchar 
type of column but after the conversion, it becomes binary type.
*/
 
-- Query 1:
-- Zero rows are returned from the query because ALFAA is not 
-- equal to ALFAa in binary format.
SELECT CustomerID
FROM orders
WHERE BINARY CustomerID='ALFAa';
 
-- Query 2:
-- Zero rows are returned from the query because ALFAA is not 
-- equal to 'ALFAA ' in binary format because 'ALFAA ' has a 
-- trailing space.
SELECT CustomerID
FROM orders
WHERE BINARY CustomerID='ALFAA ';

Next tutorial: Using Comparison Functions
Back to Section Index: Single Row Functions
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search