Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.4Mb
Man Pages (TGZ) - 202.1Kb
Man Pages (Zip) - 307.5Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Functions and Operators  /  Cast Functions and Operators

13.10 Cast Functions and Operators

Table 13.14 Cast Functions and Operators

BINARY Cast a string to a binary string
CAST() Cast a value as a certain type
CONVERT() Cast a value as a certain type

Cast functions and operators enable a value of given data type to be converted to a value of a different type.

CONVERT() with a USING clause provides a way to convert data between different character sets:

CONVERT(expr USING transcoding_name)

In MySQL, transcoding names are the same as the corresponding character set names.


SELECT CONVERT(_latin1'Müller' USING utf8);
INSERT INTO utf8_table (utf8_column)
    SELECT CONVERT(latin1_column USING utf8) FROM latin1_table;

You can also use CONVERT() without USING or CAST() to convert strings between different character sets:

CONVERT(string, character_data_type CHARACTER SET charset_name)
CAST(string AS character_data_type CHARACTER SET charset_name)



If you specify CHARACTER SET X as just shown, the resulting character set and collation are X and the default collation of X. If you do not specify CHARACTER SET X, the resulting character set and collation are defined by the character_set_connection and collation_connection system variables that determine the default connection character set and collation (see Section 11.1.5, “Connection Character Sets and Collations”).

A COLLATE clause is not permitted within a CONVERT() or CAST() call, but you can apply it to the function result. For example, this is legal:


But this is illegal:


Normally, you cannot compare a BLOB value or other binary string in case-insensitive fashion because binary strings use the binary character set, which has no collation with the concept of lettercase. To perform a case-insensitive comparison, use the CONVERT() or CAST() function to convert the value to a nonbinary string. Comparisons of the resulting string use its collation. For example, if the character set of the result has a case-insensitive collation, a 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 specify a particular collation for the converted string, use a COLLATE clause following the CONVERT() call:

SELECT 'A' LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci
  FROM tbl_name;

CONVERT() and CAST() can be used more generally for comparing strings that are represented in different character sets.

LOWER() (and UPPER()) are ineffective when applied to binary strings because the concept of lettercase does not apply. To perform lettercase conversion of a binary string, convert it to a nonbinary string:

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
| New York    | new york                          |

The cast functions are useful for creating a column with a specific type in a CREATE TABLE ... SELECT statement:

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

The cast 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() also changes the result if you use it as part of a more complex expression such as CONCAT('Date: ',CAST(NOW() AS DATE)).

For temporal values, there is little need to use CAST() to extract data in different formats. Instead use a function such as EXTRACT(), DATE_FORMAT(), or TIME_FORMAT(). See Section 13.7, “Date and Time Functions”.

To cast a string to a number, you normally need not do anything other than use the string value in numeric context:

mysql> SELECT 1+'1';
       -> 2

That is also true for hexadecimal and bit literals, which are treated as binary strings by default:

mysql> SELECT X'41', X'41'+0;
        -> 'A', 65
mysql> SELECT b'1100001', b'1100001'+0;
        -> 'a', 97

A string used in an arithmetic operation is converted to a floating-point number during expression evaluation.

A number used in string context is converted to a string:

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

For information about implicit conversion of numbers to strings, see Section 13.2, “Type Conversion in Expression Evaluation”.

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned by default (see Section 13.6.1, “Arithmetic Operators”). You can override this by using the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT 1 - 2;
        -> -1
        -> 18446744073709551615
        -> -1

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

The SQL mode affects the result of conversion operations. Examples:

  • If you convert a zero date string to a date, CONVERT() and CAST() return NULL and produce a warning when the NO_ZERO_DATE SQL mode is enabled.

  • For integer subtraction, if the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the subtraction result is signed even if any operand is unsigned.

For more information, see Section 6.1.8, “Server SQL Modes”.

The following list describes the available cast functions and operators:

  • BINARY expr

    The BINARY operator casts the expression following it to a binary string. A common use for BINARY is to force a comparison to be done byte by byte rather than character by character, in effect becoming case sensitive. The BINARY operator also causes trailing spaces in comparisons 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.

    For purposes of converting a string expression to a binary string, these constructs are equivalent:

    BINARY expr
    CAST(expr AS BINARY)

    If a value is a string literal, it can be designated as a binary string without performing any conversion by using the _binary character set introducer:

    mysql> SELECT 'a' = 'A';
            -> 1
    mysql> SELECT _binary 'a' = 'A';
            -> 0

    For information about introducers, see Section, “Character Set Introducers”.

    If you cast an indexed column to BINARY, MySQL may not be able to use the index efficiently.

    The BINARY operator in expressions differs in effect from the BINARY attribute in character column definitions. A character column defined with the BINARY attribute is assigned the binary (_bin) collation of the column character set. Every nonbinary character set has a _bin collation. For example, the binary collation for the utf8 character set is utf8_bin, so if the table default character set is utf8, these two column definitions are equivalent:

    CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin

    The use of CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as a binary data type. For example, the following pairs of definitions are equivalent:

    CHAR(10) CHARACTER SET binary
  • CAST(expr AS type)

    The CAST() function takes an expression of any type and produces a result value of a specified type, similar to CONVERT(). For more information, see the description of CONVERT().

    CAST() is standard SQL syntax.

  • CONVERT(expr,type), CONVERT(expr USING transcoding_name)

    The CONVERT() and CAST() functions take an expression of any type and produce a result value of a specified type.

    CONVERT(... USING ...) is standard SQL syntax. The non-USING form of CONVERT() is ODBC syntax.

    CONVERT() with USING converts 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);

    CONVERT() without USING and CAST() take an expression and a type value specifying the result type. These type values are permitted:

    • BINARY[(N)]

      Produces a string with the BINARY data type. See Section 12.4.2, “The BINARY and VARBINARY Types” 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. Values shorter than N bytes are padded with 0x00 bytes to a length of N.

    • CHAR[(N)] [charset_info]

      Produces a string with the CHAR data type. If the optional length N is given, CHAR(N) causes the cast to use no more than N characters of the argument.

      With no charset_info clause, CHAR produces a string with the default character set. To specify the character set explicitly, these charset_info values are permitted:

      • CHARACTER SET charset_name: Produce a string with the given character set.

      • ASCII: Shorthand for CHARACTER SET latin1.

      • UNICODE: Shorthand for CHARACTER SET ucs2.

      In all cases, the string has the default collation for the character set.

    • DATE

      Produces a DATE value.


      Produces a DATETIME value.

    • DECIMAL[(M[,D])]

      Produces a DECIMAL value. If the optional M and D values are given, they specify the maximum number of digits (the precision) and the number of digits following the decimal point (the scale).

    • JSON (added in MySQL 5.7.8)

      Produces a JSON value. For details on the rules for conversion of values between JSON and other types, see Comparison and Ordering of JSON Values.

    • NCHAR[(N)]

      Like CHAR, but produces a string with the national character set. See Section, “The National Character Set”.

      Unlike CHAR, NCHAR does not permit trailing character set information to be specified.


      Produces a signed INTEGER value.

    • TIME

      Produces a TIME value.


      Produces an unsigned INTEGER value.

User Comments
  Posted by anne blankert on May 29, 2006
To convert to numeric, the convert() and cast() functions are less forgiving then the implicit conversion when it comes to the data to be converted. If you want to convert "1a" or "1 apple", "2 apples", " 3 things" to 1, 1, 2 and 3 respectivly, the cast and convert function will produce an error. Instead use select 0+'1a', 0+'1 apple', 0+'2 apples', 0+' 3 things'.
  Posted by Ronald Rudy on December 5, 2006
Here's a workaround for not being able to cast/convert a value during table creation with just a create:

SELECT 1000000.001-1000000.001 as n;

This will yield a table with the following structure:

| Field | Type | Null | Key | Default | Extra |
| n | double(20,3) | | | 0.000 | |
  Posted by Mohamed Infiyaz Zaffer Khalid on January 4, 2008
The cast() function is amazingly useful when working with dates - not to mention date+time.

First, take a look at the following basic example:

select cast('2007-12-25' as DATETIME)

This naturally returns the output:
2007-12-25 00:00:00

But there are REALLY useful practical situations where we'd HAVE to use this function. Here's a case:

I want to pick all records from a table "Sales", where the "TransactionDate" field is between 25-Dec-2007 and 25-Jan-2008. Bear in mind that the field is of type DateTime.

Here's the BASIC query that I'd put in a string if I were to do it in PHP:

"SELECT * FROM Sales WHERE TransactionDate BETWEEN '$D1' and $D2"

In the above case, I'm assuming that $D1 and $D2 are posted variables in PHP. Unfortunately The above line won't work. And THIS is where we can use the CAST() function.



This does the job without a flaw!
Happy programming,

  Posted by Ice Fire on August 26, 2008
Alphanumeric strings automatically evaluate to 0 when used in a SELECT query to match against a column of type INT.

For example:

Table T1
n: INT
n_squared: INT,

n n_squared
0 0
1 1
2 4
3 9
4 16

Q1: SELECT * FROM T1 WHERE n_squared='ABC';
Q2: SELECT * FROM T1 WHERE n_squared='ABC123';

Both queries produce identical results:

n n_squared
0 0
  Posted by D'n Russler on June 3, 2009
To save others time in searching for something that wasn't immediately obvious to me...

A list of character sets is in 9.1.12. Character Sets and Collations That MySQL Supports,

  Posted by Jon Lewis on February 24, 2010
Here's another workaround for the lack of data types: create your own conversion function. I am working with a DB (unfortunately) converted from and used by MS Access. All the primary keys are therefor of type INT(10) SIGNED. When UNIONing a constant integer (SELECT 0 AS Key) with a queried value the result is of the wrong type. So, since "CAST(0 AS INT(10))" is not an option I created my own:

RETURNS INT(10) SIGNED -- here is the trick

Now, whenever I need to force a result to be of type int(10) I just run it through this function. I'm sure it would work for other types too (although a bit more involved if converting between two different classes of data such as numeric to alpha). Hope that helps.
  Posted by guillaume - on June 23, 2010
If you need to convert an id to a part of a string, the easiest way is to:

WHERE dst LIKE CONCAT("%", CAST(fs.nid as BINARY),"%")

  Posted by Darren Cassar on September 7, 2010
Note that if you need to set collation for a cast you will have to do it after you finish the cast expression as explained:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;
  Posted by ZAky ke on March 17, 2011
How do I cast column value not a string?

SELECT CONVERT(_latin1city USING utf8) from users;

Where city is a column in users table.

I need to this statement for insert statement for another table.

  Posted by Chris Wagner on September 28, 2011
One of the glaring omissions in MySQL is the inability to convert between binary strings and integers and IP's. Here is a way to do it.

Binary string to integer:
conv(hex(binfield), 16, 10)

Binary string to IP:
inet_ntoa(conv(hex(binfield), 16, 10))

  Posted by Martin Lester on November 15, 2011
Convert HEX (string) to DECIMAL


Thought that would be in the basic examples.
  Posted by Eric Kent on December 23, 2011
The comment by anne blankert on May 29 2006 is not correct, at least with the current version. In a script, both of these will return the expect value of 1:
select cast(‘1a’ as unsigned);
select 0 + ‘1a’;
However, both generate a warning (1292 Truncated incorrect … value).
When casting a character value to a numeric, the character value must be a properly formed number representation. In a script you can simply ignore the warning, the script will return the expected value and continue. However, in a stored procedure, even if you handle the warning with a condition handler, the statement:
set iValue = 0 + ‘1a’;
will return null.
To extract the numeric part of a string, you need to create a function that will parse it using substr().
Sign Up Login You must be logged in to post a comment.