MySQL Type Conversion: CAST and CONVERT

1. Type Conversion in MySQL

Type conversion is the process of converting a value from one data type to another. It is crucial when:

• Performing arithmetic operations on string-based numbers.
• Comparing values of different types.
• Formatting data for output or reporting.

MySQL offers two main functions for explicit type conversion:

1. CAST(expression AS data_type)
2. CONVERT(expression, data_type)

Both achieve similar results but differ slightly in syntax and usage.

2. CAST(): Explicitly Converting Data Types

The CAST() function explicitly converts an expression from one type to another.

Syntax

CAST(expression AS data_type)

• expression: The value or column to convert.
• data_type: The target data type (e.g., CHAR, DATE, SIGNED, DECIMAL).

Example 1: Converting String to Integer

SELECT CAST('123' AS SIGNED) AS converted_number;

Explanation:

1. '123' is a string.
2. CAST(... AS SIGNED) converts it to an integer.
3. Result: 123 (numeric type), which can now be used in arithmetic operations.

Example 2: Converting Number to String

SELECT CAST(2026 AS CHAR) AS converted_text;

Explanation:

• Converts the number 2026 into a string.
• Result: '2026' (text type).
• Useful for concatenation or formatting in reports.

Example 3: Converting String to Date

SELECT CAST('2026-04-27' AS DATE) AS converted_date;

• Converts the string '2026-04-27' to a proper DATE type.
• Allows date arithmetic, like adding or subtracting days.

3. CONVERT(): Another Way to Change Data Types

The CONVERT() function is similar to CAST but offers additional flexibility in some contexts, especially for character set conversion.

Syntax

CONVERT(expression, data_type)

• expression: The value or column to convert.
• data_type: Target type (like CHAR, SIGNED, DECIMAL, etc.).

Example 1: Converting String to Integer

SELECT CONVERT('456', SIGNED) AS converted_number;

• Converts the string '456' to a numeric type.
• Works identically to CAST in this case.

Example 2: Converting Number to String

SELECT CONVERT(2026, CHAR) AS converted_text;

• Converts numeric 2026 to '2026'.
• Can be used in concatenation with other strings.

Example 3: Using CONVERT for Character Set Conversion

SELECT CONVERT('Hello World' USING utf8) AS utf8_text;

• Converts text to a different character set (UTF-8 in this case).
• Useful when dealing with multilingual databases or exporting data.

4. Key Differences Between CAST and CONVERT

Principle: Both functions convert values explicitly, preventing unexpected results during operations or comparisons. While CAST is standard SQL, CONVERT offers extra flexibility for character sets.

5. Practical Use Cases of Type Conversion

A. Calculating with String Numbers

SELECT CAST('100' AS DECIMAL) + CAST('50.5' AS DECIMAL) AS total;

Explanation:

• Converts string numbers '100' and '50.5' to numeric types.
• Performs addition: 100 + 50.5 = 150.5.

B. Formatting Dates as Strings

SELECT CONCAT('Today is ', CAST(CURDATE() AS CHAR)) AS message;

• Converts the date returned by CURDATE() into a string for concatenation.
• Output: Today is 2026-04-27.

C. Ensuring Accurate Comparisons

SELECT *
FROM orders
WHERE order_id = CAST('101' AS SIGNED);

• Prevents type mismatch when order_id is numeric but input is a string.
• Ensures accurate query results.

6. Best Practices for Type Conversion in MySQL

1. Use explicit conversion: Avoid relying on implicit type conversion to prevent unexpected results.
2. Prefer CAST for portability: CAST follows SQL standards and works in most relational databases.
3. Use CONVERT for character set conversions: Ideal for multilingual applications.
4. Validate data types: Ensure source data is compatible with the target type to avoid errors.