CAST for type conversion

Concepts covered: Type Casting

CAST(expression AS type) converts a value from one type to another and returns the result in the new type, leaving the stored column untouched. It serves 2 situations: data that arrived in the wrong type, like the text qty_text column from section 0, and an operation that needs a type the column does not have, like dividing integers without losing the fraction. The stock table returns with its text quantities.

SELECT sku,
  qty_text,
  CAST(qty_text AS INTEGER) AS qty,
  CAST(qty_text AS INTEGER) * 2 AS reorder_qty,
  CAST(qty_text AS INTEGER) / 4 AS int_quarter,
  CAST(qty_text AS DOUBLE) / 4 AS exact_quarter
FROM stock

qty multiplies as a number, and the 2 quarter columns show what the target type does to arithmetic: a value cast to INTEGER divides with truncation, so 9 becomes 2, and the same value cast to DOUBLE keeps the fraction, 2.25. Casting one side of the division is enough, because the database widens the integer side to match the wider one.

The number-to-text direction

Casting from a number to text is equally common, and its most useful case is a join between 2 systems that stored the same key as different types. The warehouse's shipments table keys each row by order_ref, an indexed VARCHAR holding values like '1042', while the orders system stores order_id as an INTEGER. Joining on s.order_ref = CAST(o.order_id AS VARCHAR) converts the integer side, so the database can still find each order_ref through its index. The reverse, CAST(s.order_ref AS INTEGER) = o.order_id, converts every stored reference before comparing, and the index on order_ref goes unused.

SELECT o.order_id, s.carrier
FROM orders o
JOIN shipments s
  ON s.order_ref = CAST(o.order_id AS VARCHAR)

Text equality is exact, character for character. If the warehouse writes references zero-padded, as '00001042', then CAST(1042 AS VARCHAR) produces '1042' and the join matches nothing, with no error to say why. Read a few stored values before writing the join. And once a number has become text it sorts as text, so sort by the numeric column rather than by its text cast, or 10 lands before 9 again.

When CAST fails

CAST('abc' AS INTEGER) has no sensible answer, and strict databases such as PostgreSQL stop the whole query with invalid input syntax for type integer. One bad row out of 10 million is enough. Lenient engines return 0 or NULL and carry on, which is quieter and worse, because the bad value becomes a zero inside a sum. A cast of 3.9 to INTEGER splits the same way: 4 in PostgreSQL, which rounds, and 3 in SQLite, which truncates. CAST is the right call when the source is known to be clean, a column exported by a system that validated it. For external files, TRY_CAST returns NULL on the rows that cannot convert, and the intermediate lesson covers it.

Watching the cast fix the sort

The sort from section 0 ends here. The animation computes the cast for each of the 5 rows, then orders the result by the new integer column.

SELECT sku, CAST(qty_text AS INTEGER) AS qty
FROM stock
ORDER BY qty

Each qty cell is produced before ORDER BY runs, because the database evaluates the SELECT expressions and then sorts the rows it has, so sorting by the alias sorts by the integer. The 5 rows land as 3, 9, 10, 25, 100, the order a warehouse manager expected from the start.

The same query, descending, assembled from tiles. Two of the tiles do not belong, and each would put the text ordering back.

Build a query that returns each SKU with its quantity as an integer, highest quantity first.

Do

• Declare the type by what the value is: a count is INTEGER, a code is VARCHAR, a flag is BOOLEAN.
• Cast one side of an integer division to DOUBLE when the fraction matters.
• Put the cast on the literal or the smaller side of a comparison, so the indexed column stays as stored.
• Use BIGINT for any id column that counts events, clicks or log lines.
• Sort an unfamiliar numeric column once to confirm it holds numbers.

Avoid

• Store a quantity, amount or id as text because the source file arrived that way.
• Rely on '100' + '50' returning 150; the next database, or the next bad row, will not.
• Put a number with a leading zero, such as a zip code, in an INTEGER column.
• Spell a flag as 'true', 'Y' or 1 when BOOLEAN is available.
• Cast a column of untrusted text with CAST alone; one bad row stops the query.

About this lesson

This section is part of the Data Types: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.