w3resource

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() function: 

>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



  

    EMPLOYEE_ID
    FIRST_NAME
    LAST_NAME
    EMAIL
    PHONE_NUMBER
    HIRE_DATE
    JOB_ID
    SALARY
    COMMISSION_PCT
    MANAGER_ID
    DEPARTMENT_ID
  

  
  
  

    100
    Steven
    King
    SKING
    515.123.4567
    17-Jun-87
    AD_PRES
    24000
    
    
    90
  

  

    101
    Neena
    Kochhar
    NKOCHHAR
    515.123.4568
    21-Sep-89
    AD_VP
    17000
    
    100
    90
  

  

    102
    Lex
    De Haan
    LDEHAAN
    515.123.4569
    13-Jan-93
    AD_VP
    17000
    
    100
    90
  

  

    103
    Alexander
    Hunold
    AHUNOLD
    590.423.4567
    3-Jan-90
    IT_PROG
    9000
    
    102
    60
  

  

    104
    Bruce
    Ernst
    BERNST
    590.423.4568
    21-May-91
    IT_PROG
    6000
    
    103
    60
  

  

    105
    David
    Austin
    DAUSTIN
    590.423.4569
    25-Jun-97
    IT_PROG
    4800
    
    103
    60
  

  

    106
    Valli
    Pataballa
    VPATABAL
    590.423.4560
    5-Feb-98
    IT_PROG
    4800
    
    103
    60
  

  

    107
    Diana
    Lorentz
    DLORENTZ
    590.423.5567
    7-Feb-99
    IT_PROG
    4200
    
    103
    60
  

  

    108
    Nancy
    Greenberg
    NGREENBE
    515.124.4569
    17-Aug-94
    FI_MGR
    12000
    
    101
    100
  

  

    109
    Daniel
    Faviet
    DFAVIET
    515.124.4169
    16-Aug-94
    FI_ACCOUNT
    9000
    
    108
    100
  

  

    ...........
  

  

    206
    William
    Gietz
    WGIETZ
    515.123.8181
    7-Jun-94
    AC_ACCOUNT
    8300
    
    205
    110

View the table

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)

PREV : MySQL Full-Text Search Functions
NEXT : MySQL Information Functions



Follow us on Facebook and Twitter for latest update.