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

Cleaning Messy Data

The job nobody talks about.

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

Helen's database is neat and tidy. Real data usually isn't. Ask any data analyst, and they'll tell you they spend a huge part of their time cleaning data. Extra spaces, random capital letters, blanks that should be nulls, and numbers stored as text. Let's learn to clean it.

Cleaning Messy Data
From the video: Cleaning Messy Data
  • Real data is messy
  • Analysts spend hours cleaning
  • Spaces, capitals, blanks

A messy table

Helen ran a sign up form on her website for a book club. Here's the script that creates the table, with five sign ups. Type it in once, and the table stays in your database. In this module, every example builds on it. Run it. At first glance, it looks fine. But look closer.

setup.sqlsql
CREATE TABLE IF NOT EXISTS signups (
    name   TEXT,
    email  TEXT,
    city   TEXT,
    amount TEXT
);
INSERT INTO signups VALUES
  ('  ana lopez ', 'ANA@Mail.com ', 'dublin',   '12.50'),
  ('Ben Ortiz',    'ben@mail.com',  'London ',  '$8'),
  ('ana lopez',    'ana@mail.com',  'Dublin',   '12.5'),
  ('Cara Diaz',    '',              'NEW YORK', 'n/a'),
  ('Dev Patel',    'dev@mail.com',  NULL,       '20');

SELECT * FROM signups;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚     name     โ”‚     email     โ”‚   city   โ”‚ amount โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚   ana lopez  โ”‚ ANA@Mail.com  โ”‚ dublin   โ”‚ 12.50  โ”‚
โ”‚ Ben Ortiz    โ”‚ ben@mail.com  โ”‚ London   โ”‚ $8     โ”‚
โ”‚ ana lopez    โ”‚ ana@mail.com  โ”‚ Dublin   โ”‚ 12.5   โ”‚
โ”‚ Cara Diaz    โ”‚               โ”‚ NEW YORK โ”‚ n/a    โ”‚
โ”‚ Dev Patel    โ”‚ dev@mail.com  โ”‚          โ”‚ 20     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Spot the mess

Here's what's wrong. Ana Lopez signed up twice, once with spaces around her name, and her email in capitals. Cities have mixed capitals and a trailing space. Cara's email is an empty string, not a null. And the amount column has a dollar sign, and the text n slash a.

DuplicatesAna signed up twice
Spaces & capitals' ana lopez ', 'NEW YORK'
Empty text'' is not NULL
Numbers as text'$8', 'n/a'

Hidden spaces

Spaces are invisible, so let's make them visible. We wrap each name in square brackets, and measure its length. Run it. The first ana lopez has two spaces before, and one after. Her name is twelve characters long, instead of nine.

clean.sqlsql
SELECT '[' || name || ']' AS name_in_brackets,
       LENGTH(name)       AS length
FROM signups
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ name_in_brackets โ”‚ length โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ [  ana lopez ]   โ”‚ 12     โ”‚
โ”‚ [Ben Ortiz]      โ”‚ 9      โ”‚
โ”‚ [ana lopez]      โ”‚ 9      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Messy data looks fine at first glance. Brackets and length reveal hidden spaces. Next, fixing text.

Key takeaways
  • Spaces, capitals
  • Blanks, numbers as text
  • Duplicates
Next up: Cleaning Text โ€” TRIM, LOWER, and UPPER