Data cleaning: SQL patterns and examples
Finding duplicates, handling nulls, standardising text and converting types. Clean first and measure second, or the same customer is counted three times.
- Find Duplicate RowsFinding duplicates groups rows by the columns that should be unique and lists the groups that appear more than once.
- Remove Duplicates, Keep the LatestRemoving duplicates numbers the rows in each duplicate group, keeps the newest one and marks the rest for deletion.
- Handle NULL ValuesHandling nulls means deciding how missing values are treated in counts, averages and divisions, using COALESCE and NULLIF.
- Trim and Standardise TextStandardising text trims spaces, fixes case and strips stray characters so the same value is spelled one way before you group or join on it.
- Extract the Email DomainExtracting the email domain takes the part after the @ sign, so you can group contacts by company or spot free-mail addresses.
- Convert Text to a DateConverting text to a date parses strings such as 31/12/2026 into real dates, so you can sort, filter and group them by period.