MySQL Tutorial/Cast Functions Operators/CAST

CAST() also changes the result if you use it as part of a more complex expression such as CONCAT("Date: ",CAST(NOW() AS DATE)).

   <source lang="sql">

mysql> mysql> SELECT CAST(1-2 AS UNSIGNED);</source>

CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name)

The CAST() and CONVERT() functions take a value of one type and produce a value of another type.

The type can be one of the following values:

1. BINARY[(N)]
2. CHAR[(N)]
3. DATE
4. DATETIME
5. DECIMAL
6. SIGNED [INTEGER]
7. TIME
8. UNSIGNED [INTEGER]

CONVERT() with USING is used to convert data between different character sets.

17. 3. CAST 17. 3. 1. CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name) 17. 3. 2. <A href="/Tutorial/MySQL/0340__Cast-Functions-Operators/Convertingthestringabcinthedefaultcharactersettothecorrespondingstringintheutf8characterset.htm">Converting the string "abc" in the default character set to the corresponding string in the utf8 character set:</a> 17. 3. 3. <A href="/Tutorial/MySQL/0340__Cast-Functions-Operators/Convertblobcolumntochartype.htm">Convert blob column to char type</a> 17. 3. 4. <A href="/Tutorial/MySQL/0340__Cast-Functions-Operators/ThecastfunctionsareusefulwhenyouwanttocreateacolumnwithaspecifictypeinaCREATESELECTstatement.htm">The cast functions are useful when you want to create a column with a specific type in a CREATE ... SELECT statement:</a> 17. 3. 5. <A href="/Tutorial/MySQL/0340__Cast-Functions-Operators/CASTisusefulforsortingENUMcolumnsinlexicalorder.htm">CAST() is useful for sorting ENUM columns in lexical order.</a> 17. 3. 6. <A href="/Tutorial/MySQL/0340__Cast-Functions-Operators/CASTalsochangestheresultifyouuseitaspartofamorecomplexexpressionsuchasCONCATDateCASTNOWASDATE.htm">CAST() also changes the result if you use it as part of a more complex expression such as CONCAT("Date: ",CAST(NOW() AS DATE)).</a> 17. 3. 7. <A href="/Tutorial/MySQL/0340__Cast-Functions-Operators/SELECTCAST1ASUNSIGNED20.htm">SELECT CAST(1 AS UNSIGNED) - 2.0;</a>

CAST() is useful for sorting ENUM columns in lexical order.

Normally, sorting of ENUM columns occurs using the internal numeric values.

Casting the values to CHAR results in a lexical sort:

   <source lang="sql">

mysql> mysql> SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR); mysql> mysql></source>

Convert blob column to char type

   <source lang="sql">

mysql> mysql> SELECT "A" LIKE CONVERT(blob_col USING latin1) FROM tbl_name; mysql> mysql></source>

Converting the string "abc" in the default character set to the corresponding string in the utf8 character set:

   <source lang="sql">

mysql> mysql> SELECT CONVERT("abc" USING utf8); +---------------------------+ | CONVERT("abc" USING utf8) | +---------------------------+ | abc | +---------------------------+ 1 row in set (0.00 sec) mysql></source>

SELECT CAST(1 AS UNSIGNED) - 2.0;

   <source lang="sql">

mysql> mysql> SELECT CAST(1 AS UNSIGNED) - 2.0; +---------------------------+ | CAST(1 AS UNSIGNED) - 2.0 | +---------------------------+ | -1.0 | +---------------------------+ 1 row in set (0.00 sec) mysql></source>

The cast functions are useful when you want to create a column with a specific type in a CREATE ... SELECT statement:

   <source lang="sql">

mysql> mysql> CREATE TABLE new_table SELECT CAST("2000-01-01" AS DATE); Query OK, 1 row affected (0.08 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> mysql> select * from new_table; +----------------------------+ | CAST("2000-01-01" AS DATE) | +----------------------------+ | 2000-01-01 | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> drop table new_table; Query OK, 0 rows affected (0.00 sec)</source>