CAST usage and examples

Overview

CAST(expression AS type) states the desired data type explicitly for arithmetic, date comparison, or output alignment. Conversion does not prove that the source data is valid: malformed text may fail, truncate, or produce a special value depending on the database and SQL mode. Profile invalid rows before bulk conversion.

Basic syntax

SELECT CAST(amount AS DECIMAL(10,2)) AS amount_num FROM orders;

Examples

CAST example

The example casts amount to DECIMAL(10,2), showing that precision and scale are part of the result contract rather than mere display formatting.

Sample table

Table：orders

orders
amount
1200
900
1500
700

Run result

result
amount_num
1200.0
900.0
1500.0
700.0

Common issues

What happens when invalid text is cast to a number?

Behavior depends on database, version, and SQL mode: it may error, return a special value, or warn. Detect invalid data before importing it.

Why not cast money to FLOAT casually?

FLOAT is approximate. Monetary values usually need DECIMAL with explicit precision and scale to avoid accumulated error.

What is the risk of casting an indexed column in WHERE?

Wrapping a column in a function may prevent a normal index lookup. Convert the constant or normalize storage types when possible.

Keep reading

CASE WHEN COALESCE IFNULL NULLIF

CAST