AI Hints
Homeโ€บSQL Tutorialโ€บCleaning Messy Data
SQL ยท Lesson 91 of 126

Blanks and Numbers

NULLIF and CAST.

โฑ 2 min readโ–ถ Video at 2:09:31๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Blanks and Numbers
From the video: Blanks and Numbers
  • 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.

clean.sqlsql
SELECT COUNT(email)                   AS emails_counted,
       COUNT(NULLIF(TRIM(email), ''))  AS real_emails
FROM signups;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

clean.sqlsql
SELECT SUM(amount) AS careless_total,
       SUM(CAST(REPLACE(amount, '$', '') AS REAL)) AS cleaned_total
FROM signups;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

clean.sqlsql
SELECT amount,
       CASE WHEN amount GLOB '*[0-9]*'
            THEN CAST(REPLACE(amount, '$', '') AS REAL)
       END AS amount_clean
FROM signups;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Removing Duplicates โ€” keep one of each