BBampaa

Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

TRIM() / UPPER() / LOWER() for text cleanup

Text-cleanup functions strip extra spaces from the edges of a value or force every letter to the same case - upper or lower - so that entries meaning the same thing actually match when compared.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Standardizing Retail Text with TRIM(), UPPER(), and LOWER()

SELECT product_id,
       UPPER(TRIM(brand)) AS brand,
       LOWER(TRIM(category)) AS category,
       price
FROM products

Result:

product_idbrandcategoryprice
P1001NIKEelectronics89.99
P1002ADIDASclothing45.5
P1003ZARAhome120.0
P1004UNIQLObeauty29.99
P1005SAMSUNGelectronics599.0

Step by step

  1. Run the query without UPPER so you can see what the unfiltered values look like.
    SELECT product_id, TRIM, LOWER(TRIM(category)) AS category, price
FROM products;
  2. UPPER converts text to all uppercase letters.
    SELECT product_id, UPPER(TRIM(brand)), LOWER(TRIM(category)) AS category, price
FROM products;
  3. AS gives the UPPER result a friendly column name so the output reads cleanly.
    SELECT product_id, UPPER(TRIM(brand)) AS brand, LOWER(TRIM(category)) AS category, price
FROM products;

Practice

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

1. Using customers, write a query to convert to lowercase the email column.
SELECT LOWER(email) FROM customers
2. Using products, write a query to remove leading and trailing spaces from the brand column.
SELECT TRIM(brand) FROM products
3. Using suppliers, write a query to convert to uppercase the country column.
SELECT UPPER(country) FROM suppliers
4. Using employees, write a query to remove leading and trailing spaces from the role column.
SELECT TRIM(role) FROM employees
5. Using reviews, write a query to remove leading and trailing spaces from the customer_id column.
SELECT TRIM(customer_id) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed