{"id":2691,"date":"2012-05-22T01:06:33","date_gmt":"2012-05-22T09:06:33","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2691"},"modified":"2024-01-04T06:53:12","modified_gmt":"2024-01-04T13:53:12","slug":"mysql-cast-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-functions\/mysql-cast-function\/","title":{"rendered":"MySQL CAST Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL CAST()<\/code> function to convert a value of a type into another type.<\/p>\n\n\n\n

Introduction to MySQL CAST function<\/h2>\n\n\n\n

The CAST()<\/code> function allows you to convert a value of one type into another type. The syntax of the MySQL CAST()<\/code> function is as follows:<\/p>\n\n\n

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