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:

SELECT CAST('test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

But this is illegal:

SELECT CAST('test' AS CHAR CHARACTER SET utf8 COLLATE utf8_bin);

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 conversion result character
set 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. For example, a comparison of these strings results in an
error because they have different character sets:

For string literals, another way to specify the character set is
to use a character set introducer (_latin1 and
_latin2 in the preceding example are instances
of introducers). Unlike conversion functions such as
CAST(), or
CONVERT(), which convert a string
from one character set to another, an introducer designates a
string literal as having a particular character set, with no
conversion involved. For more information, see
Section 11.1.3.8, “Character Set Introducers”.

Character set conversion is also useful preceding lettercase
conversion of binary strings.
LOWER() and
UPPER() are ineffective when
applied directly to binary strings because the concept of
lettercase does not apply. To perform lettercase conversion of a
binary string, first convert it to a nonbinary string:

The cast functions are 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:

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

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.)

The BINARY operator converts the
expression to a binary string. A common use for
BINARY is to force a character
string 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.

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 table default
character set and the binary (_bin)
collation of that 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) BINARY
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 the corresponding binary string data type.
For example, the following pairs of definitions are
equivalent:

CHAR(10) CHARACTER SET binary
BINARY(10)
VARCHAR(10) CHARACTER SET binary
VARBINARY(10)
TEXT CHARACTER SET binary
BLOB

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.
No padding occurs for values shorter than
N characters.

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

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:

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:

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:

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

SELECT CAST(X'01ABCDEF' AS 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().

Posted by
Sebastian Lechner
on
December 7, 2016

It is interesting to know that if you try casting a string which does not represent a valid number to integer or decimal, the CAST function returns 0: