6. SQL Data Types Analysts Actually Meet

6.4 How to Check Types with typeof() and Convert with CAST

By Ravindra Bagale Last updated: 31 October 2026

Why. Imported files often bring numbers as text, with commas or spaces. A text value compared with a number can give a surprising result.

What. CAST(value AS REAL) turns text into a number. It reads digits from the start and stops at the first character it does not understand, so '1,200.50' becomes 1.0 and 'N/A' becomes 0.0. A TEXT column compared with a number is compared as text, so '400' counts as bigger than 1000.

How. Look at raw_amount with typeof() and CAST side by side, then see the text comparison trap.

Steps

Run the first example and compare raw_amount with the cast_value column.
Find the rows where CAST gave a wrong number because of a comma or the text N/A.
Run the second example and notice that row 8 with '400' is kept by the filter.
Never trust a filter on a text column of numbers until you clean or cast it.

Examples

Example 1 · dataset: northstar_bank

SELECT txn_id, raw_amount,
       typeof(raw_amount)       AS stored_as,
       CAST(raw_amount AS REAL) AS cast_value,
       amount
FROM transactions
WHERE txn_id IN (2, 4, 13, 15, 20);

Output

txn_id	raw_amount	stored_as	cast_value	amount
2	1,200.50	text	1.0	1200.5
4	650.25	text	650.25	650.25
13	56,000	text	56.0	56000.0
15	N/A	text	0.0	91000.0
20		null		47000.0

Meaning. A leading space is fine, so ' 650.25' becomes 650.25. Commas break the number, so '1,200.50' becomes 1.0 and '56,000' becomes 56.0. 'N/A' becomes 0.0 and NULL stays NULL.

Example 2 · dataset: northstar_bank

SELECT txn_id, raw_amount
FROM transactions
WHERE raw_amount > 1000
  AND txn_id IN (3, 8, 11, 15);

Output

txn_id	raw_amount
3	8000
8	400
11	300.10
15	N/A

Meaning. All four rows pass, even 400, 300.10 and N/A. The text column turns 1000 into the text '1000', and in text order '4' comes after '1'.

Ravindra Bagale's Tip

When a filter on numbers gives strange rows, run typeof() first. The column is often TEXT.