The CAST function in MySQL is used to convert a value from one data type to another. Here’s the general syntax and examples:
Syntax for CAST
CAST(expression AS data_type)expression: The value or result you want to convert.data_type: The target data type you want to convert the value into.
Common Data Types for CAST
Data Type  | Description  | 
SIGNED | Converts to a signed integer  | 
UNSIGNED | Converts to an unsigned integer  | 
DECIMAL(M,D) | Converts to a decimal number  | 
CHAR | Converts to a string  | 
BINARY | Converts to binary data  | 
DATETIME | Converts to a datetime format  | 
TIME | Converts to a time format  | 
DATE | Converts to a date format  | 
Examples of CAST
1. Casting to Integer (SIGNED/UNSIGNED)
To convert a string containing numbers into an integer.
SELECT 
CAST('1234' AS SIGNED) AS numeric_value;Result:
numeric_value
12342. Casting to String (CHAR)
To convert numbers into a string type.
SELECT 
CAST(1234 AS CHAR) AS string_value;
Result:
string_value
12343. Casting to Decimal
To convert a string to a decimal with a specified precision.
Syntax: DECIMAL(M, D) where M is total digits and D is digits after the decimal.
SELECT 
CAST('1234.567' AS DECIMAL(6,2)) AS decimal_value;Result:
decimal_value
1234.574. Casting with REGEXP_REPLACE
When using REGEXP_REPLACE to extract numeric values, you can cast them for numeric sorting.
SELECT
    column_name,
    CAST(REGEXP_REPLACE(column_name, '[^0-9]', '') AS UNSIGNED) AS numeric_part
FROM table_name
ORDER BY numeric_part;
5. Casting to Date or Time
To convert strings into date or time formats.
SELECT CAST('2024-06-18' AS DATE) AS date_value,
       CAST('12:30:00' AS TIME) AS time_value;Result:
date_value  | time_value  | 
2024-06-18  | 12:30:00  | 
Summary
The CAST function is a simple and powerful way to convert between data types. Common usages include:
- Convert text to numbers (
SIGNED,UNSIGNED) for numeric operations or sorting. - Convert numbers to strings (
CHAR). - Convert strings to date/time formats (
DATE,TIME,DATETIME). 
It helps ensure data is handled in the right format for your query's logic!
