(mysql.info.gz) Cast Functions

 
 12.7 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
 
      `BINARY' affects the entire comparison; it can be given before
      either operand with the same result.
 
      `BINARY' was added in MySQL 3.23.0. As of MySQL 4.0.2, `BINARY
      str' is a shorthand for `CAST(str AS BINARY)'.
 
      Note that in some contexts, if you cast an indexed column to
      `BINARY', MySQL will not be able to use the index efficiently.
 
      If you want to compare a `BLOB' value or other binary string in
      case-insensitive fashion, you can do so as follows:
 
         * Before MySQL 4.1.1, use the `UPPER()' function to convert the
           binary string to uppercase before performing the comparison:
 
                SELECT 'A' LIKE UPPER(BLOB_COL) FROM TBL_NAME;
 
           If the comparison value is lowercase, convert the string
           value using `LOWER()' instead.
 
         * For MySQL 4.1.1 and up, 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 string value to a character set that is not case
           sensitive. The result is a non-binary string, so 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.
 
 
      `CONVERT()' can be used more generally for comparing strings that
      are represented in different character sets.
 
 `CAST(EXPR AS TYPE)'
 `CONVERT(EXPR,TYPE)'
 `CONVERT(EXPR USING TRANSCODING_NAME)'
      The `CAST()' and `CONVERT()' functions can be used to take a value
      of one type and produce a value of another type.
 
      The TYPE can be one of the following values:
 
         * `BINARY'
 
         * `CHAR'
 
         * `DATE'
 
         * `DATETIME'
 
         * `SIGNED [INTEGER]'
 
         * `TIME'
 
         * `UNSIGNED [INTEGER]'
 
      `BINARY' produces a binary string. See the entry for the `BINARY'
      operator in this section for a description of how this affects
      comparisons.
 
      `CAST()' and `CONVERT()' are available as of MySQL 4.0.2.  The
      `CHAR' conversion type is available as of 4.0.6.  The `USING' form
      of `CONVERT()' is available as of 4.1.0.
 
      `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 server's default character set
      to the corresponding string in the `utf8' character set:
 
           SELECT CONVERT('abc' USING utf8);
 
 
 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.
 
 * In MySQL 4.0, a `CAST()' to `DATE', `DATETIME', or `TIME' only
 marks the column to be a specific type but doesn't change the value of
 the column.
 
 As of MySQL 4.1.0, the value is converted to the correct column type
 when it's sent to the user (this is a feature of how the new protocol
 in 4.1 sends date information to the client):
 
      mysql> SELECT CAST(NOW() AS DATE);
             -> 2003-05-26
 
 As of MySQL 4.1.1, `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()'.  Date
 and time functions.
 
 To cast a string to a numeric value in numeric context, you don't
 normally have to do anything. Just 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.
 
 The handing of unsigned values was changed in MySQL 4.0 to be able to
 support `BIGINT' values properly. If you have some code that you want
 to run in both MySQL 4.0 and 3.23, you probably can't use the `CAST()'
 function. You can use the following technique to get a signed result
 when subtracting two unsigned integer columns `ucol1' and `ucol2':
 
      mysql> SELECT (ucol1+0.0)-(ucol2+0.0) FROM ...;
 
 The idea is that the columns are converted to floating-point values
 before the subtraction occurs.
 
 If you have a problem with `UNSIGNED' columns in old MySQL applications
 when porting them to MySQL 4.0, you can use the
 `--sql-mode=NO_UNSIGNED_SUBTRACTION' option when starting `mysqld'.
 However, as long as you use this option, you will not be able to make
 efficient use of the `BIGINT UNSIGNED' column type.
Info Catalog
(mysql.info.gz) Fulltext Search
(mysql.info.gz) Functions
(mysql.info.gz) Other Functions
automatically generated byinfo2html