Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 sure the results will be ordered alphabetically.