MySQL Cast functions and Operators

MySQL BINARY Operator

The BINARY operator is used to force a column comparison byte by byte rather than character by character and comparison becomes case sensitive. BINARY also causes trailing spaces to be significant.

Example:

mysql> SELECT 'x' = 'X';
+-----------+
| 'x' = 'X' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT BINARY 'x' = 'X';
+------------------+
| BINARY 'x' = 'X' |
+------------------+
|                0 |
+------------------+
1 row in set (0.05 sec)

mysql> SELECT 'x' = ' X';
+------------+
| 'x' = ' X' |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT BINARY 'x' = ' X';
+-------------------+
| BINARY 'x' = ' X' |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

MySQL CAST() function

The CAST() function is used to convert the type of an expression to a specified type. The function is similar to CONVERT() function.

Syntax:

>CAST(expr AS type))

CONVERT() function:

CONVERT(expr USING transcoding_name)

List of target types:

• BINARY[(N)]: BINARY produces a string with the BINARY data type. If the optional length N is given, BINARY(N) causes the cast to use no more than N bytes of the argument.
• CHAR[(N)]: For CHAR(N), CAST() function can not use any more than N characters of the argument.
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER]

Example: DECIMAL type

The following code converts DECIMAL values (commision_pct, 2,2) into CHAR values and displayes the first name and commission information (where commision_pct>.30) from employees table.

Sample table: employees

mysql> SELECT first_name, CONCAT('Commisison Pct. - ',CAST(commission_pct AS CHAR)) from employees WHERE commission_pct>.30;
+------------+-----------------------------------------------------------+
| first_name | CONCAT('Commisison Pct. - ',CAST(commission_pct AS CHAR)) |
+------------+-----------------------------------------------------------+
| John       | Commisison Pct. - 0.40                                    |
| Janette    | Commisison Pct. - 0.35                                    |
| Patrick    | Commisison Pct. - 0.35                                    |
| Allan      | Commisison Pct. - 0.35                                    |
+------------+-----------------------------------------------------------+
4 rows in set (0.00 sec)

Example: DATETIME type

The following SQL statement converts DATE values (hire-date) into DATETIME values and display the first name and joining date-time information from employees table.

mysql> SELECT first_name, CAST(hire_date AS DATETIME) 'Joining  Date' from employees where hire_date>('1987-09-15') ;
+------------+---------------------+
| first_name | Joining  Date       |
+------------+---------------------+
| Randall    | 1987-09-16 00:00:00 |
| Sarah      | 1987-09-17 00:00:00 |
| Britney    | 1987-09-18 00:00:00 |
| Samuel     | 1987-09-19 00:00:00 |
| Vance      | 1987-09-20 00:00:00 |
| Alana      | 1987-09-21 00:00:00 |
| Kevin      | 1987-09-22 00:00:00 |
| Donald     | 1987-09-23 00:00:00 |
| Douglas    | 1987-09-24 00:00:00 |
| Jennifer   | 1987-09-25 00:00:00 |
| Michael    | 1987-09-26 00:00:00 |
| Pat        | 1987-09-27 00:00:00 |
| Susan      | 1987-09-28 00:00:00 |
| Hermann    | 1987-09-29 00:00:00 |
| Shelley    | 1987-09-30 00:00:00 |
| William    | 1987-10-01 00:00:00 |
+------------+---------------------+
16 rows in set (0.04 sec)

CAST() and CONVERT(... USING ...) are standard SQL syntax. The non-USING form of CONVERT() is ODBC syntax. CONVERT() function with USING clause is used to convert data between different character sets. In the following example the MySQL statement converts the string 'bird', the default character set to the corresponding string in the utf8 character set :

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

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

mysql> CREATE TABLE table2 SELECT CAST('2012-12-12' AS DATE);
Query OK, 1 row affected (1.40 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TABLE2;
+----------------------------+
| CAST('2012-12-12' AS DATE) |
+----------------------------+
| 2012-12-12                 |
+----------------------------+
1 row in set (0.04 sec)

- 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:

Code:

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

MySQL supports arithmetic with both signed and unsigned 64-bit values. When using numeric operators (such as + or -) and if one of the operands is an unsigned integer, the result will be unsigned by default.

mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
+---------------------------------------+
| CAST(CAST(1-2 AS UNSIGNED) AS SIGNED) |
+---------------------------------------+
|                                    -1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(1-2 AS UNSIGNED);
+-----------------------+
| CAST(1-2 AS UNSIGNED) |
+-----------------------+
|  18446744073709551615 |
+-----------------------+
1 row in set (0.00 sec)

If any operand is a floating-point value, the result will be a floating-point value.

mysql> SELECT CAST(11 AS UNSIGNED) - 3.0;
+----------------------------+
| CAST(11 AS UNSIGNED) - 3.0 |
+----------------------------+
|                        8.0 |
+----------------------------+
1 row in set (0.00 sec)

Previous: MySQL Full-Text Search Functions
Next: MySQL Information Functions