Summary: in this tutorial, you will learn how to use the MySQL CAST()
function to convert a value of a type into another type.
Introduction to MySQL CAST function
The CAST()
function allows you to convert a value of one type into another type. The syntax of the MySQL CAST()
function is as follows:
CAST(expression AS TYPE);
Code language: SQL (Structured Query Language) (sql)
The target type can be any one of the following types: BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, or UNSIGNED.
In practice, you often use the CAST()
function to return a value with a specified type for comparison in the WHERE, JOIN, and HAVING clauses.
MySQL CAST function examples
Let’s take a look at some examples of using the CAST()
function.
1) Simple MySQL CAST function examples
In the following example, MySQL converts a string into an integer implicitly before doing a calculation:
SELECT (1 + '1')/2;
Code language: SQL (Structured Query Language) (sql)
Output:
+-------------+
| (1 + '1')/2 |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)
Code language: JavaScript (javascript)
To explicitly convert a string into an integer, you use the CAST()
function as the following statement:
SELECT (1 + CAST('1' AS UNSIGNED))/2;
Code language: SQL (Structured Query Language) (sql)
+-------------------------------+
| (1 + CAST('1' AS UNSIGNED))/2 |
+-------------------------------+
| 1.0000 |
+-------------------------------+
1 row in set (0.01 sec)
Code language: JavaScript (javascript)
The following statement explicitly converts an integer into a string and concatenates the string with another string:
SELECT CONCAT('MySQL CAST example #',CAST(2 AS CHAR));
Code language: SQL (Structured Query Language) (sql)
+------------------------------------------------+
| CONCAT('MySQL CAST example #',CAST(2 AS CHAR)) |
+------------------------------------------------+
| MySQL CAST example #2 |
+------------------------------------------------+
1 row in set (0.00 sec)
Code language: PHP (php)
2) Using MySQL CAST() function with table data examples
Let’s take a look at the orders
table in the sample database:
The following query retrieves the orders whose required dates are in January 2003:
SELECT
orderNumber,
requiredDate
FROM
orders
WHERE
requiredDate BETWEEN '2003-01-01' AND '2003-01-31';
Code language: SQL (Structured Query Language) (sql)
Because the data type of the requireDate
column is DATE
, MySQL has to convert the literal strings: '2003-01-01'
and '2003-01-31'
into TIMESTAMP values before evaluating the WHERE
condition.
To ensure safety, you can use the CAST()
function to explicitly convert a string into a TIMESTAMP
value, as follows:
SELECT
orderNumber,
requiredDate
FROM
orders
WHERE
requiredDate BETWEEN CAST('2003-01-01' AS DATETIME)
AND CAST('2003-01-31' AS DATETIME);
Code language: SQL (Structured Query Language) (sql)
+-------------+--------------+
| orderNumber | requiredDate |
+-------------+--------------+
| 10100 | 2003-01-13 |
| 10101 | 2003-01-18 |
| 10102 | 2003-01-18 |
+-------------+--------------+
3 rows in set (0.03 sec)
Code language: JavaScript (javascript)
The following statement converts DEC
values into CHAR
values and uses the results as the arguments of the CONCAT function:
SELECT productName,
CONCAT('Prices(',
CAST(buyprice AS CHAR),
',',
CAST(msrp AS CHAR),
')') prices
FROM products;
Code language: SQL (Structured Query Language) (sql)
+---------------------------------------------+-----------------------+
| productName | prices |
+---------------------------------------------+-----------------------+
| 1969 Harley Davidson Ultimate Chopper | Prices(48.81,95.70) |
| 1952 Alpine Renault 1300 | Prices(98.58,214.30) |
| 1996 Moto Guzzi 1100i | Prices(68.99,118.94) |
| 2003 Harley-Davidson Eagle Drag Bike | Prices(91.02,193.66) |
| 1972 Alfa Romeo GTA | Prices(85.68,136.00) |
| 1962 LanciaA Delta 16V | Prices(103.42,147.74) |
| 1968 Ford Mustang | Prices(95.34,194.57) |
| 2001 Ferrari Enzo | Prices(95.59,207.80) |
....
Summary
- Use the MySQL
CAST()
function to convert a value with any type into a value with a specified type.