(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