MySQL CAST Function

MySQL CAST Function

The CAST() function in MySQL is used to convert a value from one data type to another. It is part of the SQL standard, making it a robust and flexible tool for data type conversions.

Syntax

CAST(expression AS target_data_type)

• expression: The value or column to be converted.
• target_data_type: The desired data type to which the value should be converted.

Supported Data Types

The CAST() function supports the following target data types:

1. Numeric Types:

   • DECIMAL, SIGNED, UNSIGNED

2. String Types:

   • CHAR, BINARY, NCHAR, CHARACTER

3. Date/Time Types:

   • DATE, DATETIME, TIME

4. JSON:

   • Converts data to JSON format.

Examples

1. Casting to Numeric Types

Convert a string to a number:

SELECT CAST('123.45' AS DECIMAL(10, 2)) AS decimal_value;

Output: 123.45

2. Casting to String

Convert a numeric value to a string:

SELECT CAST(12345 AS CHAR) AS string_value;

Output: '12345'

3. Casting to Date/Time Types

Convert a string to a DATE:

SELECT CAST('2025-01-24' AS DATE) AS date_value;

Output: 2025-01-24

4. Casting to JSON

Convert a string to JSON:

SELECT CAST('{"name": "John", "age": 30}' AS JSON) AS json_value;

Output: {"name": "John", "age": 30}

Using CAST() in Queries

1. Using in SELECT Clause

Convert column data types for better readability or formatting:

SELECT order_id, CAST(total_amount AS CHAR) AS amount_as_text
FROM orders;

2. Using in WHERE Clause

Compare data by converting types:

SELECT * 
FROM orders
WHERE CAST(order_date AS CHAR) = '2025-01-24';

3. Using in GROUP BY

Ensure consistency in grouping by casting:

SELECT CAST(order_date AS DATE) AS order_day, COUNT(*) AS total_orders
FROM orders
GROUP BY CAST(order_date AS DATE);

Key Considerations

1. Truncation or Rounding: When converting to numeric types, values may be truncated or rounded depending on the target type and precision.

2. Invalid Conversions: If the CAST() function cannot convert a value, it may result in NULL or an error, depending on the context.

3. Performance Impact: Conversions may add overhead to queries, especially when applied to large datasets.

Difference Between CAST() and CONVERT()

Feature	CAST()	CONVERT()
SQL Standard	Compliant	Not SQL standard (MySQL-specific syntax).
Syntax	CAST(expression AS type)	CONVERT(expression, type)
Use Case	Preferred for portability.	Useful in MySQL-specific projects.

Example with CONVERT():

SELECT CONVERT('123.45', DECIMAL(10, 2)) AS decimal_value;

Conclusion

The CAST() function in MySQL is a powerful tool for converting data types within queries. Its versatility across numeric, string, date/time, and JSON formats makes it an essential function for data transformation and type compatibility in SQL operations.