Cleaning Messy Data
The job nobody talks about.
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.

- 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.
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;โโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโฌโโโโโโโโโโโฌโโโโโโโโโ โ 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.
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.
SELECT '[' || name || ']' AS name_in_brackets,
LENGTH(name) AS length
FROM signups
LIMIT 3;โโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโ โ 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