Table 11.13. Cast Functions
BINARY
| Cast a string to a binary string |
CAST[]
| Cast a value as a certain type |
Convert[]
| Cast a value as a certain type |
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 asBINARY
orBLOB
.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 ';
-> 0In a comparison,
BINARY
affects the entire operation; it can be given before either operand with the same result.BINARY
is shorthand forstr
CAST[
.str
AS BINARY]Note that in some contexts, if you cast an indexed column to
BINARY
, MySQL is not able to use the index efficiently.CAST[
expr
AStype
]The
CAST[]
function takes a value of one type and produce a value of another type, similar toCONVERT[]
. See the description ofCONVERT[]
for more information.CONVERT[
,expr
,type
]CONVERT[
expr
USINGtranscoding_name
]The
CONVERT[]
andCAST[]
functions take a value of one type and produce a value of another type.The
type
can be one of the following values:BINARY[[
N
]]CHAR[[
N
]]DATE
DATETIME
DECIMAL[[
M
[,D
]]]SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
BINARY
produces a string with theBINARY
data type. See Section 10.4.2, “TheBINARY
andVARBINARY
Types” for a description of how this affects comparisons. If the optional lengthN
is given,BINARY[
causes the cast to use no more thanN
]N
bytes of the argument. Values shorter thanN
bytes are padded with0x00
bytes to a length ofN
.CHAR[
causes the cast to use no more thanN
]N
characters of the argument.CAST[]
andCONVERT[... USING ...]
are standard SQL syntax. The non-USING
form ofCONVERT[]
is ODBC syntax.CONVERT[]
withUSING
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 default character set to the corresponding string in theutf8
character set:SELECT CONVERT['abc' USING utf8];
Normally, you cannot compare a BLOB
value or other binary string in case-insensitive fashion because 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 value to a nonbinary string. If the character set of the result has a case-insensitive collation, the LIKE
operation is not case sensitive:
SELECT 'A' LIKE CONVERT[blob_col
USING latin1] FROMtbl_name
;
To use a different character set, substitute its name for latin1
in the preceding statement. To ensure that a case-insensitive collation is used, specify a COLLATE
clause following the
CONVERT[]
call.
CONVERT[]
can be used more generally for comparing strings that are represented in different character sets.
The cast functions are useful when you want to create a column with a specific type in a CREATE TABLE ...
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:
SELECTenum_col
FROMtbl_name
ORDER BY CAST[enum_col
AS CHAR];
CAST[
is the same thing as str
AS BINARY]BINARY
. str
CAST[
treats the expression as a string with the default character set. expr
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]]
.
You should not use CAST[]
to extract data in different formats but instead use string functions like LEFT[]
or EXTRACT[]
. See Section 11.6, “Date and Time Functions”.
To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to 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 +
or -
] 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.
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.
How do I convert an int to a string in MySQL?
MySQL CAST[] Function The CAST[] function converts a value [of any type] into the specified datatype.
Can we convert int to string in SQL?
The CONCAT implicitly converts values into strings. If you need to concatenate different data types, CONCAT is the best option instead of using the + character. Note: If you need to convert int into a string to compare, the cast and convert should be used.
What is CAST [] in MySQL?
The MySQL CAST[] function is used for converting a value from one datatype to another specific datatype. The CAST[] function accepts two parameters which are the value to be converted and the datatype to which the value needs to be converted.
How do I convert a date to a string in MySQL?
In SQL Server, you can use CONVERT function to convert a DATETIME value to a string with the specified format. ... Mapping SQL Server Datetime Style to MySQL Format..