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.
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.
Syntax of MySQL CAST()
CAST(EXPRESSION AS DATATYPE)
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
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
SELECT CAST("MySQLCode" AS NCHAR);
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
3. CAST to DATE
SELECT CAST("11-11-11" AS DATE);
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);
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
5. CAST to TIME
SELECT CAST("09:33:33" AS TIME);
In the above example, we have used a string that is converted to
SELECT CAST(093333 AS TIME);
In the above example, we have used a number that is converted to
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
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);
SELECT CAST(69 AS YEAR);
When the number ranges from 70 to 99 it will be in the 1900s.
SELECT CAST(70 AS YEAR);
SELECT CAST(99 AS YEAR);
When a string is “0”, the returned value will be 2000.
SELECT CAST("0" AS YEAR);
When the number is 0, the returned value will be 0.
SELECT CAST(0 AS YEAR);
If the number is out of any of the ranges mentioned above will result in the output is
7. Other types
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
You can read more about
You can read more about National Character Set here.