Why Data Cleaning Matters

Data analysts spend a significant amount of time cleaning data before performing analysis. SQL provides many functions that help standardize, validate, and transform raw data into a consistent format.

---

Common SQL Data Cleaning Tasks

Task	SQL Function
Remove leading/trailing spaces	TRIM()
Convert to uppercase	UPPER()
Convert to lowercase	LOWER()
Replace text	REPLACE()
Handle NULL values	COALESCE() or IFNULL()
Convert data types	CAST()
Extract part of text	LEFT(), RIGHT(), SUBSTRING()
Find text	LIKE, INSTR()
Remove duplicates	DISTINCT
Update incorrect values	UPDATE

---

Remove Extra Spaces

SELECT TRIM(city)
FROM cities;

---

Standardize Capitalization

SELECT UPPER(state_code)
FROM cities;

SELECT LOWER(email)
FROM customers;

---

Replace Characters

SELECT REPLACE(phone,'-','')
FROM customers;

---

Convert Data Types

SELECT CAST(population AS UNSIGNED)
FROM cities;

---

Remove Duplicate Records

SELECT DISTINCT city, state
FROM cities;

---

Handle Missing Values

SELECT COALESCE(state,'Unknown')
FROM cities;

---

Real-World Example

Before cleaning:

city	state_code	population
" seattle "	wa	1,250,000
"NEW YORK"	ny	8,258,000

After cleaning:

city	state_code	population
Seattle	WA	1250000
New York	NY	8258000

Example:

SELECT
    TRIM(city) AS city,
    UPPER(TRIM(state_code)) AS state_code,
    REPLACE(population, ',', '') AS population
FROM cities;

---

Excel vs SQL Data Cleaning

Task	Excel	SQL
Remove spaces	TRIM()	TRIM()
Uppercase	UPPER()	UPPER()
Lowercase	LOWER()	LOWER()
Replace characters	SUBSTITUTE()	REPLACE()
Convert to number	VALUE()	CAST()
Remove duplicates	Remove Duplicates	DISTINCT

---

Key Takeaways

• Excel is useful for cleaning small datasets.
• SQL is better for cleaning large datasets stored in a database.
• Many data-cleaning concepts are the same in both Excel and SQL.