MySQL CAST Function

Objective: Discover how to convert a value from one data type to another supported data type using the CAST() function in MySQL.

MySQL CAST() Function

The CAST function follows a simple syntax, as shown below:

CAST(expr AS type [array]);

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.

select cast(100 as char) as out_;

The previous example uses the CAST() function to convert an int type to a string.

Output:

out_|
----+
100 |

Example 2

We can also use the CAST() function to convert a value to date type, as shown in the following example:

select cast('2022-10-10' as date) as out_;

The resulting value is shown below:

out_ |
----------+
2022-10-10|

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:

select cast('10-10-2022' as date) as out_;

In this case, the previous format follows an incorrect date format. MySQL will return an error, as shown below:

Incorrect datetime value: '10-10-2022'

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:

select cast('100' as decimal) as out_;

Output:

out_|
----+
100|

Example 4: Convert String to Float

select cast('100.2' as float) as out_;

Output:

out_ |
-----+
100.2|

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:

select concat(server_name, ' - ', cast(installed_version as char) from stack_mapping sm;

Resulting table:

Conclusion

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.

MySQL CAST Function