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

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

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:

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

Convert blob column to char type

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

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

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

SELECT CAST(1 AS UNSIGNED) - 2.0;

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>

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

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)