MySQL CAST

Parameters of MySQL CAST() Function

There are two parameters of the MySQL cast() function. The first parameter is Expression and the second parameter is Datatype.

• Expression: It is defined as the value that needs to be converted using the MySQL cast() function. It is a required parameter.
• Datatype: It is defined as the type of data (such as int, char, decimal, or binary ) in which the expression needs to be converted. It is also a required parameter.

Return Value of MySQL CAST() Function

The MySQL cast() function returns the expression converted into the specified data type. We get the return value according to the parameter that we describe in the syntax of the function. For example, if we set the datatype expression as char. Then, the output will be of char type.

Exceptions of MySQL CAST() Function

MySQL CAST() Function can be used with most of the clauses in MySQL, but the version of MySQL is important for using the MySQL cast function. Only the versions from MySQL 4.0 and afterward supports the MySQL cast function. Here is a list of the MySQL versions that supports the MySQL cast function.

• MySQL 8.0
• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0

How Does the MySQL CAST() Function work?

We know that the MySQL cast function is used to convert some values into other specified data types. There are two parameters in the MySQL cast() function inside the parenthesis. Whatever value we put in place of the Expression parameter gets converted into the specified data type. And whatever the type of data we place in the Datatype parameter, the function converts the value into that particular data type.

The MySQL cast() function works for some specific data types that are described below using the table.

Examples

Till now, you have learned about what is MySQL cast function, various aspects of the function, and how they work. Now, let us see some examples of the MySQL cast function to get a better understanding of the MySQL cast function. All the examples are provided with output and explanation.

Example 1. Value into DATE In this example, we will simply convert a value into a DATE datatype. Given below is the query to convert a value in the date format into a DATE datatype.

Output:

Explanation: Here, we are using the CAST function with the select clause. Inside the parentheses, we have defined some integer value in the format of date, as a Value parameter. Then we defined the Type parameter as DATE. Then we get a date value as the output of the function.

Example 2. Value into SIGNED datatype In this example, we will convert some integer values into a Signed datatype. The signed value is the integer value with the sign which shows whether that particular value is negative or positive. We will use the below query to perform this:

Output:

Explanation: Here we have defined the Value parameter as 10-15. After the query gets executed, an arithmetic operation will be performed here to evaluate the value of that defined integer. Then the function takes this value and assigns a sign (positive or negative) according to the result of the Value parameter and then provides the output. Here the resultant output is negative, so the result is -5.

Example 3. Value into SIGNED datatype In this example, we will convert some integer value into an Unsigned datatype. An unsigned value is an integer value without any sign to represent whether that particular value is negative or positive. Just the modulus of that integer value is displayed as output.

Output:

Explanation: Here we have defined the Value parameter as 10-15. After the query gets executed, an arithmetic operation will be performed here to evaluate the value of that defined integer. Then the function takes this value but does not assign any sign (positive or negative) according to the result of the Value parameter and then provides the output. Here the resultant output is negative, but the result is 5 without a negative sign assigned to it.

Example 4. String into Integer explicitly While working with MySQL, sometimes we need to convert the string datatype into an integer datatype explicitly. Then we use the function as given below:

Output:

Explanation: Here, there are two parentheses used to evaluate the MySQL cast function. We have used the function with the SELECT clause and inside the first parameter, there is just a 5 integer value, which is an unsigned value. Then in the next parenthesis, the integer value 5 is used as the signed value which is positive 5. So, the total evaluated result is calculated as "5+5/4".

Example 5. Integer into String datatype and concatenation In this example, we will perform two operations in the MySQL cast function. First, we will convert the integer value into a string value and then we will perform concatenation of the output with another specified string.

Output:

Explanation: Here, we used the CONCAT command just before the parenthesis starts. Inside the parenthesis, we have assigned "This is an example of CAST Function, example Number ##" as the value parameter. And then we used the cast function to convert integer 5 into a string value. After the evaluation, CONCATENATION is done on the output.

Example 6. Cast function with tables In this example, we will learn how the MySQL cast function works with a MySQL table. Suppose there is a table namely Orders as shown in the below image:

Then we will use the following query to perform the MySQL cast function on the particular table.

Output:

Explanation: Here, we can see that the column namely Order_Date is of DATE datatype. So, we have used the MySQL cast function to get the name of all the products that have been ordered within a particular range of time.