Name
CAST()
Synopsis
CAST(expression
AStype
[CHARACTER SETcharacter_set
])
Use this function to convert a value from one data type to
another. This function is available as of version 4.0.2 of MySQL. The
data type given as the second argument can be
BINARY
, CHAR
,
DATE
, DATETIME
, SIGNED
[INTEGER]
, TIME
, or UNSIGNED
[INTEGER]
. BINARY
converts a string to a
binary string.
CHAR
conversion is available as of version
4.0.6 of MySQL. This function is similar to CONVERT(). Optionally, you can add CHARACTER
SET
to use a different character set from the default for
the value given. The default is drawn from the system variables
character_set_connection
and
collation_connection
.
As an example, suppose we want to retrieve a list of courses for
the current semester (Spring) and their locations, sorting them
alphabetically by their building name. Unfortunately, the building
names are in an ENUM()
column because we’re
at a small college. Since they’re not in alphabetical order in the
column definition, they won’t be sorted the way we want. Instead, they
will be sorted in the lexical order of the column definition, that is,
the order they are listed in the ENUM()
column of the table definition. Using CAST()
in the
WHERE
clause can resolve this:
SELECT course_id, course_name, CONCAT(building, '-', room_num) AS location FROM courses WHERE year = YEAR(CURDATE()) AND semester = 'spring' ORDER BY CAST(building AS CHAR);
By using the CAST()
function to treat
the values of building
as a CHAR
data type, we make ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.