MySQL CAST() Function
The CAST function follows a simple syntax, as shown below:
The function accepts the expression and the target data type as the parameters. It then converts the value to the specified target data type and returns the expression.
The function can convert the input expression to various supported data types, including BINARY, CHAR, DATE, DATETIME, DECIMAL, DOUBLE, FLOAT, NCHAR, UNSIGNED, SIGNED, REAL, etc.
Example 1: MySQL CAST() Function
Let us look at some example usage of the cast function in MySQL.
The previous example uses the CAST() function to convert an int type to a string.
We can also use the CAST() function to convert a value to date type, as shown in the following example:
The resulting value is shown below:
It is good to keep in mind that a value must follow the correct format and can be converted to the specified type. For example, attempting to convert an incorrect date value to the cast function will return NULL.
An example is shown below:
In this case, the previous format follows an incorrect date format. MySQL will return an error, as shown below:
In some cases, MySQL will return a NULL value.
Example 3: Convert String to Int
We can also convert a given string type to int, as shown in the following example:
Example 4: Convert String to Float
Example 5: Using CAST() Function on Table Column
We can also use the CAST() function with a table column. For example, suppose we have a table as shown below:
We can use the CONCAT() and CAST() function to convert the values of the installed_version column, as shown in the query below:
SQL Server - 15.0 |
Elasticsearch - 8.4 |
Redis - 6.0 |
PostgreSQL - 14.5 |
MySQL - 8.0 |
This tutorial taught the basics of working with the CAST() function to convert a value from one type to another. Several examples are provided to highlight converting String to Int and String to Float.