(mysql.info) cast-functions
Info Catalog
(mysql.info) fulltext-search
(mysql.info) functions
(mysql.info) other-functions
12.8 Cast Functions and Operators
=================================
* `BINARY'
The `BINARY' operator casts the string following it to a binary
string. This is an easy way to force a column comparison to be
done byte by byte rather than character by character. This causes
the comparison to be case sensitive even if the column isn't
defined as `BINARY' or `BLOB'. `BINARY' also causes trailing
spaces to be significant.
mysql> SELECT 'a' = 'A';
-> 1
mysql> SELECT BINARY 'a' = 'A';
-> 0
mysql> SELECT 'a' = 'a ';
-> 1
mysql> SELECT BINARY 'a' = 'a ';
-> 0
In a comparison, `BINARY' affects the entire operation; it can be
given before either operand with the same result.
`BINARY STR' is shorthand for `CAST(STR AS BINARY)'.
Note that in some contexts, if you cast an indexed column to
`BINARY', MySQL is not able to use the index efficiently.
* `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:
* `BINARY[(N)]'
* `CHAR[(N)]'
* `DATE'
* `DATETIME'
* `DECIMAL'
* `SIGNED [INTEGER]'
* `TIME'
* `UNSIGNED [INTEGER]'
binary-varbinary:: for a description of how this affects
comparisons. If the optional length N is given, `BINARY[N]' causes
the cast to use no more than N bytes of the argument. As of MySQL
5.0.17, values shorter than N bytes are padded with `0x00' bytes
to a length of N.
`CHAR[N]' causes the cast to use no more than N characters of the
argument.
The `DECIMAL' type is available as of MySQL 5.0.8.
`CAST()' and `CONVERT(... USING ...)' are standard SQL syntax. The
non-`USING' form of `CONVERT()' is ODBC syntax.
`CONVERT()' with `USING' is used to convert data between different
character sets. In MySQL, transcoding names are the same as the
corresponding character set names. For example, this statement
converts the string `'abc'' in the default character set to the
corresponding string in the `utf8' character set:
SELECT CONVERT('abc' USING utf8);
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
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 `+') 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.
mysql> SELECT CAST(1-2 AS UNSIGNED)
-> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
-> -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.
Info Catalog
(mysql.info) fulltext-search
(mysql.info) functions
(mysql.info) other-functions
automatically generated byinfo2html