CAST( )

Use this MySQL function to convert a value from one datatype to another.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CAST(expression AS type [CHARACTER SET character_set])

Explanation

Use this function to convert a value from one datatype to another. This function is available as of version 4.0.2 of MySQL. The datatype 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.

Examples

As an example, suppose we want to retrieve a list of courses for the current semester (Spring) and their locations, but 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 wouldn't be sorted the way we want. Instead, they will be sorted in the lexical order of the column definition, 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 alphabetically ordered.