12.8. Cast Functions and Operators

Normally, you cannot compare a BLOB value or other binary string in case-insensitive fashion because binary strings have no character set, and thus no concept of lettercase. To perform a case-insensitive comparison, use the CONVERT() function to convert the value to a non-binary string. If the character set of the result has a case-insensitive collation, the LIKE operation is not case sensitive:

SELECT 'A' LIKE CONVERT(blob_col USING latin1) FROM tbl_name;

To use a different character set, substitute its name for latin1 in the preceding statement. To ensure that a case-insensitive collation is used, specify a COLLATE clause following the CONVERT() call.

CONVERT() can be used more generally for comparing strings that are represented in different character sets.

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

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

The functions also can be 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:

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

CAST(str AS BINARY) is the same thing as BINARY str. CAST(expr AS CHAR) treats the expression as a string with the default character set.

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

You should not use CAST() to extract data in different formats but instead use string functions like LEFT() or EXTRACT(). See Section 12.5, “Date and Time Functions”.

To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number:

mysql> SELECT 1+'1';
       -> 2

If you use a number in string context, the number automatically is converted to a BINARY string.

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned. You can override this by using the SIGNED and UNSIGNED cast operators to cast the operation to a signed or unsigned 64-bit integer, respectively.

        -> 18446744073709551615
        -> -1

Note that if either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule. (In this context, DECIMAL column values are regarded as floating-point values.)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

If you are using a string in an arithmetic operation, this is converted to a floating-point number.

If you convert a “zero” date string to a date, CONVERT() and CAST() return NULL when the NO_ZERO_DATE SQL mode is enabled. As of MySQL 5.0.4, they also produce a warning.