CAST

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

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
1234

2. Casting to String (CHAR)

To convert numbers into a string type.

SELECT 
CAST(1234 AS CHAR) AS string_value;

Result:

string_value
1234

3. 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.57

4. 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:

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!