Data Cleaning in SQL

SELECT
  customer_id,
  name,
  COALESCE(email, 'no-email@example.com') AS email,
  COALESCE(phone, 'No phone') AS phone,
  COALESCE(city, 'Unknown') AS city
FROM customers_raw;

-- Convert invalid discount values (-1, 0) to NULL
SELECT
  product_id,
  name,
  price,
  NULLIF(discount, -1) AS discount_clean1,
  NULLIF(NULLIF(discount, -1), 0) AS discount_clean2
FROM products_raw;

SELECT
  id,
  CONCAT('[', name, ']') AS original,
  CONCAT('[', TRIM(name), ']') AS trimmed,
  CONCAT('[', LTRIM(name), ']') AS left_trimmed,
  CONCAT('[', RTRIM(name), ']') AS right_trimmed
FROM names_raw;

SELECT
  'John DOE' AS original,
  UPPER('John DOE') AS uppercase,
  LOWER('John DOE') AS lowercase,
  CONCAT(
    UPPER(SUBSTRING('john doe', 1, 1)),
    LOWER(SUBSTRING('john doe', 2))
  ) AS title_case;

-- Standardize phone numbers to digits only
SELECT
  customer_id,
  phone AS original,
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    phone, '(', ''), ')', ''), '-', ''), '.', ''), ' ', ''
  ) AS digits_only
FROM phones_raw;

SELECT
  'Alice Smith' AS full_name,
  SUBSTRING('Alice Smith', 1, 5) AS first_name,
  SUBSTRING('Alice Smith', 7) AS last_name,
  LEFT('Alice Smith', 5) AS left_5,
  RIGHT('Alice Smith', 5) AS right_5;

-- Extract username and domain
SELECT
  user_id,
  email,
  SUBSTRING_INDEX(email, '@', 1) AS username,
  SUBSTRING_INDEX(email, '@', -1) AS domain
FROM emails;

-- Find invalid phone numbers (not 10 digits)
SELECT
  customer_id,
  phone,
  LENGTH(phone) AS phone_length,
  CASE
    WHEN LENGTH(phone) = 10 THEN 'Valid'
    ELSE 'Invalid'
  END AS validation
FROM phones_cleaned;

-- Convert string to number, handle errors
SELECT
  order_id,
  total AS original,
  CASE
    WHEN total REGEXP '^[0-9]+(\.[0-9]+)?$'
    THEN CAST(total AS DECIMAL(10,2))
    ELSE NULL
  END AS total_numeric
FROM orders_raw;

SELECT
  id,
  date_string,
  STR_TO_DATE(date_string, '%Y-%m-%d') AS iso_date,
  STR_TO_DATE(date_string, '%m/%d/%Y') AS us_date,
  STR_TO_DATE(date_string, '%d-%m-%Y') AS eu_date
FROM dates_raw;

-- Remove exact duplicates
SELECT DISTINCT email, name
FROM customers_dupes
ORDER BY name;

-- Keep earliest customer_id per email
SELECT customer_id, email, name
FROM (
  SELECT
    customer_id,
    email,
    name,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn
  FROM customers_dupes
) ranked
WHERE rn = 1;

SELECT
  product_id,
  -- Standardize category
  UPPER(TRIM(category)) AS category_clean,
  -- Fix negative prices
  CASE
    WHEN price < 0 THEN NULL
    ELSE price
  END AS price_clean,
  -- Fix negative stock
  CASE
    WHEN stock < 0 THEN 0
    ELSE stock
  END AS stock_clean,
  -- Add status flag
  CASE
    WHEN price < 0 OR stock < 0 THEN 'NEEDS REVIEW'
    WHEN stock = 0 THEN 'OUT OF STOCK'
    ELSE 'OK'
  END AS data_quality
FROM products_messy;

SELECT
  id,
  -- Clean name: trim, title case
  CASE
    WHEN TRIM(name) = '' OR name IS NULL THEN 'Unknown'
    ELSE CONCAT(
      UPPER(SUBSTRING(TRIM(LOWER(name)), 1, 1)),
      LOWER(SUBSTRING(TRIM(name), 2))
    )
  END AS name_clean,
  -- Clean email: lowercase, handle NULL
  COALESCE(LOWER(TRIM(email)), 'no-email@example.com') AS email_clean,
  -- Clean phone: remove formatting
  CASE
    WHEN TRIM(phone) = '' OR phone IS NULL THEN NULL
    ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      phone, '(', ''), ')', ''), '-', ''), '.', ''), ' ', ''
    )
  END AS phone_clean,
  -- Clean city: title case
  CASE
    WHEN city IS NULL OR TRIM(city) = '' THEN 'Unknown'
    ELSE CONCAT(
      UPPER(SUBSTRING(TRIM(LOWER(city)), 1, 1)),
      LOWER(SUBSTRING(TRIM(city), 2))
    )
  END AS city_clean,
  -- Clean state: uppercase, 2 letters
  UPPER(TRIM(COALESCE(state, 'XX'))) AS state_clean
FROM customers_import;

CREATE TABLE customers_clean AS
SELECT
  id AS customer_id,
  -- [all cleaning logic from above]
FROM customers_import
WHERE email IS NOT NULL;  -- Exclude rows without emails

-- Find records with issues
SELECT
  'Missing email' AS issue,
  COUNT(*) AS count
FROM customers_import
WHERE email IS NULL OR TRIM(email) = ''

UNION ALL

SELECT
  'Missing name',
  COUNT(*)
FROM customers_import
WHERE name IS NULL OR TRIM(name) = ''

UNION ALL

SELECT
  'Invalid phone length',
  COUNT(*)
FROM customers_import
WHERE LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  phone, '(', ''), ')', ''), '-', ''), '.', ''), ' ', ''
)) != 10;