SQL data types and CAST: converting values safely

A value has both content and a type

Two values can look similar while behaving very differently in SQL. Consider 8500 and '8500'. The first may be stored as a number. The second is text containing the characters 8, 5, 0, 0. To a person reading a report, they look almost identical. To SQL, their data types can change how they are compared, sorted, added, filtered, joined, and aggregated.

This becomes especially important when data arrives from CSV files, APIs, spreadsheets, or external systems where values may initially be stored as text. Imagine a raw import of our orders data:

The column names deliberately include _text. Although unit_price_text = '8500' looks like a number, SQL currently sees it as text. Similarly, ordered_at_text = '2026-07-31 18:30:00' looks like a timestamp, but it is still text until it is converted to a timestamp type.

Common data type families

SQL databases provide many data types, but several broad families appear frequently.

Different database systems provide different type names and capabilities, but the main idea remains the same: choose a data type that represents what the value actually means.

Converting values with CAST

SQL provides CAST for explicit type conversion. The general form is CAST(value AS target_type). For example, CAST('8500' AS INTEGER) takes the text '8500' and converts it into the numeric value 8500. Now SQL can treat it as a number.

Converting text to numbers

Suppose we want to calculate the order amount, quantity × unit price. Both values currently come from text columns, quantity_text and unit_price_text. We can convert them:

SELECT
    order_id,
    customer,
    CAST(quantity_text AS INTEGER) AS quantity,
    CAST(unit_price_text AS INTEGER) AS unit_price,
    CAST(quantity_text AS INTEGER)
        * CAST(unit_price_text AS INTEGER) AS order_amount
FROM raw_orders
ORDER BY order_id;

The values now participate in numeric multiplication rather than text operations.

The numeric filter that is actually comparing text

Suppose the requirement is return products whose unit price is greater than 1,000. Someone writes the query below. It may run successfully, but the comparison is between text values ('750', '3200', '1200', '8500', '90', '1800'), not numbers. Text comparison follows character ordering rules rather than numeric magnitude.

SELECT
    order_id,
    customer,
    unit_price_text
FROM raw_orders
WHERE unit_price_text > '1000'
ORDER BY unit_price_text;

The fix: convert before the numeric comparison

Make the required type explicit:

SELECT
    order_id,
    customer,
    CAST(unit_price_text AS INTEGER) AS unit_price
FROM raw_orders
WHERE CAST(unit_price_text AS INTEGER) > 1000
ORDER BY unit_price;

Now SQL is comparing 1200 > 1000, 1800 > 1000, 3200 > 1000, and 8500 > 1000 as numbers.

Sorting text numbers has the same problem

ORDER BY unit_price_text DESC sorts text. It does not mean highest numeric price first. To request numeric ordering, ORDER BY CAST(unit_price_text AS INTEGER) DESC, and the correct sort becomes:

8500
3200
1800
1200
750
90

A column containing digits is not automatically a numeric column.

Choosing the right numeric type

Not every numeric value should become an integer. Consider discount_text = '300.50'. This cannot be represented accurately as an integer without losing the fractional component. For values requiring decimal precision, use an appropriate exact numeric type:

SELECT
    order_id,
    CAST(discount_text AS DECIMAL(10, 2)) AS discount_amount
FROM raw_orders;

DECIMAL(10, 2) commonly means a decimal value with up to ten total digits, two of which are after the decimal point. For money-like calculations, exact decimal types such as DECIMAL or NUMERIC are generally preferable to binary floating-point types when exact decimal behaviour matters.

Number of items          -> INTEGER
Monetary amount          -> DECIMAL / NUMERIC
Approximate measurement  -> floating-point type

Choose the target type according to the meaning of the data. Do not choose a type only because the conversion succeeds.

Invalid values cannot always be cast

Consider order 1004, where discount_text = ''. An empty string is not a valid decimal number in many database systems, so CAST(discount_text AS DECIMAL(10, 2)) can fail. The same problem appears with values such as 'N/A', 'unknown', '₹500', and '10 dollars'. These are text values, not clean numeric values. CAST converts compatible representations; it does not automatically clean arbitrary source data.

Normalizing empty strings before conversion

Suppose the business has decided an empty discount_text means the discount is missing. We can first turn the empty string into NULL, then cast:

CAST(
    NULLIF(TRIM(discount_text), '')
    AS DECIMAL(10, 2)
)

For '', NULLIF produces NULL, and the cast of NULL remains NULL. This avoids treating an empty string as though it were a number.

Implicit conversions

Sometimes SQL converts values automatically. This is called an implicit conversion or implicit cast. Suppose order_id is an integer column and a query contains WHERE order_id = '1001'. The right side looks like text, but some databases may determine that the comparison should be numeric and convert one side automatically. The query may work, and that convenience can make implicit conversions tempting. But their rules vary between database systems and contexts.

Why relying on implicit conversion is risky

Consider WHERE numeric_column = text_value. SQL must decide: should the number become text, or should the text become a number? The answer depends on the database’s type-resolution rules. If the text contains '100', a conversion may work. If it contains '100A', the same conversion may fail. Relying on implicit conversion is:

• Harder to understand.
• More dependent on database-specific behaviour.
• More fragile when data changes.
• Harder to debug.

When different data types meet and the intended conversion matters, make it explicit. Instead of relying on WHERE order_id = '1001', prefer a comparison where both sides clearly represent the same type, WHERE order_id = 1001, or use an explicit cast when conversion is genuinely required.

Conversion is not the same as formatting

Suppose amount = 1234.5. Casting it to text with CAST(amount AS TEXT) changes its type. It does not necessarily mean 1,234.50, ₹1,234.50, or $1,234.50. Those are formatting requirements. Formatting currency, separators, padding, and locale-specific presentation is a different problem, often handled by database-specific formatting functions or by the application displaying the result.

CAST         changes the data type
Formatting   changes how the value is presented

Converting text to dates

Date values also commonly arrive as text. Our raw table contains ordered_at_text = '2026-07-31 18:30:00'. We can convert this to a timestamp:

SELECT
    order_id,
    CAST(ordered_at_text AS TIMESTAMP) AS ordered_at
FROM raw_orders;

Now SQL can perform timestamp operations such as ordered_at + INTERVAL '7 days' or ordered_at >= TIMESTAMP '2026-07-01 00:00:00'. A text representation of a timestamp does not automatically give us reliable timestamp semantics. Converting it to the proper type does.

Convert to DATE when you need a calendar date

Suppose we have 2026-07-31 18:30:00 and need only 2026-07-31. We can cast a timestamp to a date:

SELECT
    order_id,
    CAST(
        CAST(ordered_at_text AS TIMESTAMP)
        AS DATE
    ) AS order_date
FROM raw_orders;

If the column is already a timestamp, the expression is simpler: CAST(ordered_at AS DATE). The result contains the calendar date without the time-of-day component.

Comparing values after conversion

Once data is converted to the correct type, SQL can apply the correct semantics. CAST(unit_price_text AS INTEGER) > 1000 asks a numeric question, and CAST(order_date_text AS DATE) >= DATE '2026-07-01' asks a calendar-date question.

Data types matter in joins too

Suppose one table stores customer_id as an integer (1, 2, 3) while another stores it as text ('1', '2', '3'). A join such as ON table_a.customer_id = table_b.customer_id now compares different types. Depending on the database, SQL may perform an implicit conversion, require an explicit conversion, or produce an error.

How to spot data-type problems

Look carefully at data types when:

When something behaves strangely, ask what the actual data type of the expression is. Do not rely only on what the value looks like.

Ask four questions when converting

One more prediction

Consider these values stored in a text column. Requirement: return orders whose unit price is greater than 1,000. Which condition best expresses the requirement?

-- Option A
WHERE unit_price_text > '1000'

-- Option B
WHERE CAST(unit_price_text AS INTEGER) > 1000

-- Option C
WHERE unit_price_text LIKE '%1000%'

-- Option D
WHERE CAST(1000 AS TEXT) < unit_price_text

Summary

SQL values have types, and those types affect how operations behave. 8500 as a number is not the same thing as '8500' stored as text. Use CAST when an explicit conversion is required: CAST(unit_price_text AS INTEGER) for whole numbers, CAST(discount_text AS DECIMAL(10, 2)) for decimals, CAST(ordered_at_text AS TIMESTAMP) for timestamps, CAST(value AS DATE) for dates, and CAST(order_id AS TEXT) for text.

Next, we will move into semi-structured data and learn how SQL works with JSON objects, nested fields, and arrays.