The MySQL CAST function is used to explicitly convert an expression or a value from one data type to another. This function is particularly useful when you need to convert data types explicitly, ensuring that the result is of the desired type.

Syntax

The basic syntax of the CAST function is as follows:

CAST(expression AS data_type)

expression: The value or expression that you want to convert.
data_type: The target data type to which you want to convert the expression.

Example

Let’s consider a scenario where you have a column with values stored as strings, but you need to perform numeric operations on those values. In such a case, you can use the CAST() function to convert the string values to numeric types.

-- Create a sample table
CREATE TABLE sales (
    product_name VARCHAR(50),
    revenue_str VARCHAR(20)
);

-- Insert sample data
INSERT INTO sales (product_name, revenue_str) VALUES
    ('Product A', '500.25'),
    ('Product B', '300.75'),
    ('Product C', '750.50');

-- Query using CAST() to convert revenue_str to DECIMAL
SELECT
    product_name,
    revenue_str,
    CAST(revenue_str AS DECIMAL(10, 2)) AS revenue_numeric
FROM
    sales;

In this example, the revenue_str column is initially stored as a string. The CAST function is used to convert it to the DECIMAL data type with two decimal places (DECIMAL(10, 2)). The result is a new column named revenue_numeric, which now contains the numeric representation of the revenue values.

Note

You can use various data types with the CAST function, such as INT, CHAR, DATE, etc.
The length and precision specified in the target data type (e.g., DECIMAL(10, 2)) can be adjusted based on your specific requirements.
It’s essential to ensure that the conversion makes sense and won’t result in data loss or unexpected behavior.

The CAST function is not limited to strings; it can be used for conversions between different data types.
Remember to adapt the data types and expressions according to your specific use case and database schema.