Lesson 44 of 12120 min read

Type Conversion in SQL: CAST() and CONVERT() in MySQL

Learn how to explicitly convert data types in MySQL using CAST() and CONVERT(), including common conversion scenarios and pitfalls.

Author: CodersNexus

Type Conversion in SQL: CAST() and CONVERT() in MySQL

MySQL often converts data types automatically behind the scenes — comparing a number to a string, for instance — but relying on implicit conversion can lead to surprising results or silent bugs. CAST() and CONVERT() let you take explicit, deliberate control over type conversion whenever it matters.

What Are CAST() and CONVERT()?

CAST(expression AS target_type) and CONVERT(expression, target_type) both explicitly convert a value from one data type to another, such as turning a text string into a number, a string into a date, or a number into formatted text. They are functionally very similar in MySQL, differing mainly in syntax and in CONVERT's additional ability to handle character set conversion.

What You'll Learn

  • Convert values explicitly between text, numeric, and date types using CAST.
  • Understand the syntax differences between CAST and CONVERT.
  • Use CONVERT specifically for character set conversion.
  • Recognize when implicit type conversion can cause subtle bugs.

Key Terms to Know

  • Explicit conversion: Deliberately converting a value's type using CAST or CONVERT.
  • Implicit conversion: MySQL automatically converting a value's type behind the scenes during an operation or comparison.
  • CAST(): A standard SQL function for explicit type conversion.
  • CONVERT(): A MySQL function for explicit type conversion, also supporting character set conversion.

CAST(): The Standard Type Conversion Syntax

CAST('42' AS UNSIGNED) explicitly converts the text string '42' into the numeric value 42, useful whenever a column is stored as text but needs to be treated numerically for calculation or sorting. CAST('2026-06-24' AS DATE) converts a properly formatted text string into an actual DATE value, enabling correct date comparisons and arithmetic.

Common target types for CAST include SIGNED/UNSIGNED (integers), DECIMAL(p,s), CHAR, DATE, DATETIME, and TIME — MySQL requires the target type to be one of a specific supported list rather than any arbitrary column type name.

CONVERT(): MySQL's Equivalent, Plus Character Set Conversion

CONVERT(expression, target_type) achieves the exact same result as CAST(expression AS target_type) for standard type conversion — they're functionally interchangeable for this purpose, and the choice between them is mostly stylistic.

CONVERT has an additional capability CAST doesn't: converting between character sets, using CONVERT(expression USING character_set), such as CONVERT(legacy_text USING utf8mb4) to convert text stored in an older character set into utf8mb4, which matters when working with legacy data imported from systems using a different encoding.

Implicit Conversion and Why Explicit Conversion Matters

MySQL frequently performs implicit type conversion automatically — comparing a VARCHAR column to a number, for example, will silently convert one side to match the other, which usually works but can occasionally produce confusing results, especially with non-numeric text being implicitly treated as 0 in some contexts, or subtle sorting differences when numbers are compared as text.

Explicit conversion with CAST or CONVERT removes this ambiguity, making the intended type change clear in the query itself, and is especially important when working with data that started out poorly typed (like numbers stored in a VARCHAR column) and needs reliable, correct sorting or arithmetic.

Visual Summary

Picture a shipment of goods arriving labeled only in a foreign currency and a foreign measurement system. Implicit conversion is like a clerk eyeballing the labels and guessing a rough local equivalent on the fly, which mostly works but occasionally produces an odd result. CAST and CONVERT are like handing the clerk an official, deliberate conversion chart and asking them to convert every label explicitly and correctly before anything gets processed further.

CAST vs CONVERT

AspectCAST()CONVERT()
SyntaxCAST(expr AS type)CONVERT(expr, type)
Standard type conversionYesYes, equivalent result
Character set conversionNot supportedCONVERT(expr USING charset)
ANSI SQL standardYesMySQL-specific syntax, though concept exists elsewhere

SQL Example

-- Converting a text-stored number into an actual numeric type for correct sorting
SELECT product_code, CAST(numeric_part AS UNSIGNED) AS numeric_value
FROM legacy_products
ORDER BY CAST(numeric_part AS UNSIGNED);

-- Converting a text string into a proper DATE for comparison
SELECT order_id
FROM raw_import
WHERE CAST(order_date_text AS DATE) >= '2026-01-01';

-- CONVERT performing the same standard conversion as CAST
SELECT CONVERT(price_text, DECIMAL(10,2)) AS price
FROM legacy_products;

-- CONVERT performing a character set conversion
SELECT CONVERT(legacy_description USING utf8mb4) AS clean_description
FROM legacy_products;

The first query ensures a text-stored numeric value sorts correctly as a number rather than alphabetically as text. The second converts a text-stored date into an actual DATE type so chronological comparison works correctly. The third shows CONVERT performing the same kind of standard type conversion as CAST. The fourth demonstrates CONVERT's unique character-set conversion capability, cleaning up text from an older encoding.

Real-World Examples

  • Data migration projects use CAST extensively to convert poorly-typed legacy text columns (numbers and dates stored as VARCHAR) into properly typed columns during cleanup.
  • Reporting tools use CAST to ensure numeric sorting behaves correctly when source data was imported as text.
  • Internationalization efforts use CONVERT USING to standardize text from legacy systems into a consistent utf8mb4 encoding.
  • ETL pipelines use CAST/CONVERT at each transformation step to guarantee data lands in the target warehouse with correct, consistent types.

Best Practices and Pro Tips

  • Use explicit CAST or CONVERT whenever sorting or comparing a numeric or date value that's stored as text, rather than relying on MySQL's implicit conversion behavior.
  • Prefer CAST for standard type conversions for slightly better portability across different SQL database systems, reserving CONVERT specifically for its unique character set conversion capability.
  • When converting text to a numeric or date type, validate the source data first — CAST on genuinely malformed text can silently produce 0, NULL, or unexpected truncated values depending on MySQL's SQL mode rather than raising a clear error.

Common Mistakes to Avoid

  • Relying on implicit type conversion for sorting numeric-looking text, leading to alphabetical rather than numeric sort order (e.g., '10' sorting before '2').
  • Forgetting that CAST's target type list is limited to specific supported types, and trying to CAST to an arbitrary type name that MySQL doesn't recognize for this purpose.
  • Using CAST on malformed or inconsistent text data without first validating or cleaning it, leading to silent, incorrect conversion results rather than an obvious error.
  • Confusing CONVERT's character-set conversion syntax (CONVERT(expr USING charset)) with its standard type-conversion syntax (CONVERT(expr, type)) — they use different argument structures.

Interview Questions

Q1. What is the difference between CAST and CONVERT in MySQL?

For standard type conversion, CAST(expr AS type) and CONVERT(expr, type) are functionally equivalent. CONVERT additionally supports character set conversion using CONVERT(expr USING charset), a capability CAST does not have.

Q2. Why might relying on implicit type conversion for sorting cause problems?

If a numeric-looking value is stored as text, sorting without explicit conversion compares it alphabetically rather than numerically, causing results like '10' sorting before '2', which doesn't match the expected numeric order.

Q3. How would you convert a text string into a proper DATE value for comparison?

Using CAST(date_text AS DATE), which explicitly converts the string into an actual DATE type, enabling correct chronological comparisons and date arithmetic rather than unreliable text-based comparison.

Q4. When would you specifically need CONVERT instead of CAST?

When converting a string's character set, such as standardizing legacy text into utf8mb4 using CONVERT(text USING utf8mb4), since this specific capability isn't available through CAST.

Practice MCQs

1. Which function supports character set conversion in MySQL that the other does not?

  1. CAST
  2. CONVERT
  3. Both support it equally
  4. Neither supports it

Answer: B. CONVERT

Explanation: CONVERT supports CONVERT(expr USING charset) for character set conversion, a capability CAST does not provide.

2. What problem does explicit type conversion (CAST/CONVERT) solve when sorting numeric-looking text?

  1. It speeds up the query
  2. It ensures numeric values sort numerically rather than alphabetically
  3. It removes duplicate rows
  4. It changes the column's stored data type permanently

Answer: B. It ensures numeric values sort numerically rather than alphabetically

Explanation: Without explicit conversion, text-stored numbers sort alphabetically, causing incorrect ordering; CAST/CONVERT fixes this by treating the value as an actual number for sorting purposes.

3. Which syntax correctly converts a value to a DECIMAL type using CAST?

  1. CAST(price USING DECIMAL(10,2))
  2. CAST(price AS DECIMAL(10,2))
  3. CONVERT(price AS DECIMAL(10,2))
  4. CAST(price, DECIMAL(10,2))

Answer: B. CAST(price AS DECIMAL(10,2))

Explanation: CAST uses the AS keyword to specify the target type, distinguishing it from CONVERT's comma-based syntax for standard conversions.

Quick Revision Points

  • CAST(expr AS type) and CONVERT(expr, type) are functionally equivalent for standard type conversion.
  • CONVERT(expr USING charset) is unique to CONVERT, for character set conversion.
  • Explicit conversion avoids subtle bugs from implicit conversion, especially for sorting and date comparisons.
  • CAST's target type must be one of MySQL's specific supported conversion types.

Conclusion

  • Explicit type conversion removes ambiguity that implicit conversion can quietly introduce.
  • CAST and CONVERT are largely interchangeable for standard conversions, with CONVERT adding character-set capability.
  • Validating source data before converting it avoids silent, incorrect conversion results.

CAST() and CONVERT() give explicit, deliberate control over type conversion in MySQL, converting text to numbers or dates, and in CONVERT's case, between character sets — removing the ambiguity and occasional subtle bugs that come from relying on implicit conversion alone. With the full functions toolkit (string, numeric, date, aggregate, NULL-handling, conditional, and type conversion) now covered, the next lesson introduces MySQL's JSON functions for working with semi-structured data stored directly in relational columns.

Frequently Asked Questions

CAST supports a specific limited list of target types in MySQL (such as CHAR, DATE, DATETIME, DECIMAL, SIGNED, UNSIGNED, among others), and while CHAR can include a length, not every arbitrary column type definition is supported as a CAST target.

CAST only affects the value as returned within that specific query's result set; it does not modify the actual stored data in the table at all, unless explicitly combined with an UPDATE statement.

Depending on MySQL's SQL mode settings, this typically returns 0 along with a warning, rather than raising a hard error, which is exactly why validating data before relying on CAST for critical calculations matters.

CAST and CONVERT handle basic type conversion but not custom formatting; for formatted numeric display (like comma-separated thousands), MySQL's FORMAT() function is the more appropriate tool.

Explicit CAST makes the intended type change clear and self-documenting directly in the query, removing any doubt about what's happening, and protecting against subtle, version- or configuration-dependent differences in how implicit conversion might behave.