Blanks and Numbers
NULLIF and CAST.
Here are two sneaky problems. An empty string looks like a null, but it isn't, so count includes it. And numbers stored as text can give the wrong total, without any error at all.

- Empty text isn't NULL
- Text numbers mislead
- No error, wrong answer
Empty is not NULL
Count of email counts every row that isn't null. Cara's empty email is counted, even though she has no email. Null if, turns a value into null when it equals something. So null if, trimmed email, empty string, turns blanks into real nulls. Run it. Five emails counted, but only four real ones.
SELECT COUNT(email) AS emails_counted,
COUNT(NULLIF(TRIM(email), '')) AS real_emails
FROM signups;โโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโโ โ emails_counted โ real_emails โ โโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโค โ 5 โ 4 โ โโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโ
The silent wrong total
Now the scary one. If we sum the amount column directly, SQLite quietly treats dollar eight and n slash a as zero. Remove the dollar sign, and cast to a real number, and we get the true total. Run it. Forty five dollars, versus fifty three. The careless total is wrong by eight dollars, and SQL didn't warn us.
SELECT SUM(amount) AS careless_total,
SUM(CAST(REPLACE(amount, '$', '') AS REAL)) AS cleaned_total
FROM signups;โโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโ โ careless_total โ cleaned_total โ โโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโค โ 45.0 โ 53.0 โ โโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโ
Clean numbers safely
Here's a safer version. Case checks that the text contains a digit. If it does, remove the dollar sign, and cast it. If not, like n slash a, the result is null. Run it. Every amount is now a real number, or an honest null.
SELECT amount,
CASE WHEN amount GLOB '*[0-9]*'
THEN CAST(REPLACE(amount, '$', '') AS REAL)
END AS amount_clean
FROM signups;โโโโโโโโโโฌโโโโโโโโโโโโโโโ โ amount โ amount_clean โ โโโโโโโโโโผโโโโโโโโโโโโโโโค โ 12.50 โ 12.5 โ โ $8 โ 8.0 โ โ 12.5 โ 12.5 โ โ n/a โ โ โ 20 โ 20.0 โ โโโโโโโโโโดโโโโโโโโโโโโโโโ
Summary
Null if turns blanks into nulls, and cast turns text into numbers. Always check totals on messy data. Next, removing duplicates.
Key takeaways- NULLIF(x, '')
- CAST(x AS REAL)
- Check your totals