MySQL CAST Function

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)

Try It Out

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)

Try It Out

+-------------------------------+
| (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)

Try It Out

+------------------------------------------------+
| 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)

Try It Out

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)

Try It Out

+-------------+--------------+
| 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)

Try It Out

+---------------------------------------------+-----------------------+
| 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.