Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

Standardizing categories with CASE WHEN and LOWER()

Combining a case-insensitive conversion with conditional logic lets you collapse messy variations of the same value into one clean, consistent label.

Example

Standardizing Retail Categories with CASE WHEN and LOWER()

SELECT product_id, brand, price,
  CASE
    WHEN LOWER(category) = 'electronics' THEN 'Electronics'
    WHEN LOWER(category) = 'clothing' THEN 'Clothing'
    WHEN LOWER(category) = 'beauty' THEN 'Beauty'
    WHEN LOWER(category) = 'grocery' THEN 'Grocery'
    WHEN LOWER(category) = 'sports' THEN 'Fashion'
    ELSE 'Other'
  END AS clean_category
FROM products

Result:

Step by step

1. One WHEN/THEN pair handles a single condition; rows that don't match get NULL (or the ELSE if present).

   SELECT
     CASE
       WHEN LOWER(category) = 'electronics' THEN 'Electronics'
       ELSE 'Other'
     END AS category
   FROM products;;

2. Add a second WHEN - SQL checks the conditions top-down and uses the first match.

   SELECT
     CASE
       WHEN LOWER(category) = 'electronics' THEN 'Electronics'
       WHEN LOWER(category) = 'clothing' THEN 'Clothing'
       ELSE 'Other'
     END AS category
   FROM products;;

3. Add a third branch the same way. Each WHEN is checked in order; first match wins.

   SELECT
     CASE
       WHEN LOWER(category) = 'electronics' THEN 'Electronics'
       WHEN LOWER(category) = 'clothing' THEN 'Clothing'
       WHEN LOWER(category) = 'beauty' THEN 'Beauty'
       ELSE 'Other'
     END AS category
   FROM products;;

Practice

Try each one in your head first, then open the answer.

SELECT CASE WHEN LOWER(customer_id) = 'n/a' THEN NULL ELSE LOWER(customer_id) END FROM customers

SELECT CASE WHEN LOWER(brand) = 'n/a' THEN NULL ELSE LOWER(brand) END FROM products

SELECT CASE WHEN LOWER(country) = 'n/a' THEN NULL ELSE LOWER(country) END FROM suppliers

SELECT CASE WHEN LOWER(employee_id) = 'n/a' THEN NULL ELSE LOWER(employee_id) END FROM employees

SELECT CASE WHEN LOWER(review_id) = 'n/a' THEN NULL ELSE LOWER(review_id) END FROM reviews