When working with a number different of data types the MySQL CAST() function comes in handy. It will help in casting the specified expressions to a specified data type. Typecasting refers to the process of manually changing the data type of an expression.
The CONVERT() function is equivalent of the CAST() Function. To understand this further think of the CAST() Function as converting a NUMERIC datatype to CHAR datatype. The conversion only lasts during the query, which means the datatype change is temporary.
Also read: MySQL STR_TO_DATE() – Convert Date String to MySQL Date
Syntax of MySQL CAST()
CAST(EXPRESSION AS DATATYPE)Code language: SQL (Structured Query Language) (sql)You can cast expressions to many different data types in MySQL, let’s look at some of them:
Working with MySQL CAST()
Let’s now try casting different types of values into the CAST() method and converting them.
1. CAST to BINARY
SELECT CAST("MySQLCode" AS BINARY);In this example, we took a string and converted it to BINARY. Here, the type of output is in VARBINARY which is similar to VARCHAR.
2. CAST to CHAR
SELECT CAST("MySQLCode" AS CHAR);Casting as a CHAR will give us the output as a VARCHAR. The output in the above example is a VARCHAR.
SELECT CAST("MySQLCode" AS NCHAR);Code language: SQL (Structured Query Language) (sql)The NCHAR type is the same as CHAR with one difference which is it uses the National Character Set. Other than that NCHAR is similar to CHAR.
3. CAST to DATE
SELECT CAST("11-11-11" AS DATE);Code language: SQL (Structured Query Language) (sql)The DATE the type has the format of 'YYYY-MM-DD'. In this the year is specified first then the month and then the day.
4. CAST to DATETIME
SELECT CAST("11-11-11" AS DATETIME);Code language: SQL (Structured Query Language) (sql)DATETIME is similar to DATE but it also includes time. If the time isn’t specified like in the above example it will us 00:00:00 by default. The format that DATETIME uses is 'YYYY-MM-DD hh:mm:ss'.
5. CAST to TIME
Format for TIME is "hh:mm:ss"
SELECT CAST("09:33:33" AS TIME);Code language: SQL (Structured Query Language) (sql)In the above example, we have used a string that is converted to TIME.
SELECT CAST(093333 AS TIME);Code language: SQL (Structured Query Language) (sql)In the above example, we have used a number that is converted to TIME.
6. CAST to YEAR
Before looking at an example, let us understand the rules that we have to follow when casting to this type.
If we cast a four-digit number in the range of 1901 to 2155 where 2155 both are included, even a string in this range will directly be cast to the YEAR.
If a number or a string which consists of one or two digits will be cast to YEAR as follows.
When the number ranges from 1 to 69 it will be in the 2000s.
SELECT CAST(1 AS YEAR);Code language: SQL (Structured Query Language) (sql)
SELECT CAST(69 AS YEAR);Code language: SQL (Structured Query Language) (sql)When the number ranges from 70 to 99 it will be in the 1900s.
SELECT CAST(70 AS YEAR);Code language: SQL (Structured Query Language) (sql)
SELECT CAST(99 AS YEAR);Code language: SQL (Structured Query Language) (sql)When a string is “0”, the returned value will be 2000.
SELECT CAST("0" AS YEAR);Code language: SQL (Structured Query Language) (sql)When the number is 0, the returned value will be 0.
SELECT CAST(0 AS YEAR);Code language: SQL (Structured Query Language) (sql)If the number is out of any of the ranges mentioned above will result in the output is NULL.
7. Other types
DECIMALDOUBLEFLOATJSONREALSIGNED- spatial_type
UNSIGNED
Conclusion
The CAST() function is super useful and easy to implement. One thing to note is that not anything can be cast to any data type. There are rules that are to be followed in the background when converting from one data type to another. To receive the result one expects they should consider this before doing any cast operation.
You can read more about the CAST()function here.
You can read more about VARBINARY here.
You can read more about National Character Set here.