BBampaa

Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

REPLACE() for fixing inconsistent values

REPLACE() scans every value in a column, finds a specific piece of text, and swaps it out with whatever you want - including nothing, if you just want it gone. Like using find-and-replace in a word processor, but it runs across thousands of rows in an instant.

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

Example

Fixing Retail Text Values with REPLACE()

SELECT product_id, REPLACE(brand, '/US', '') AS brand_clean, category, price
FROM products

Result:

product_idbrand_cleancategoryprice
P1001NikeFashion89.99
P1002AdidasFashion74.5
P1003L'OrealBeauty22.0
P1004SamsungElectronics499.99
P1005Levi'sClothing59.95

Step by step

  1. Run the query without REPLACE so you can see what the unfiltered values look like.
    SELECT product_id, brand, category, price
FROM products;
  2. REPLACE finds a substring and substitutes it with another.
    SELECT product_id, REPLACE(brand, '/US', ''), category, price
FROM products;
  3. AS gives the REPLACE result a friendly column name so the output reads cleanly.
    SELECT product_id, REPLACE(brand, '/US', '') AS brand_clean, category, price
FROM products;

Practice

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

1. Using customers, write a query to replace 'Unknown' with 'Other' in the full_name column.
SELECT REPLACE(full_name, 'Unknown', 'Other') FROM customers
2. Using products, write a query to replace '-' with 'Other' in the product_id column.
SELECT REPLACE(product_id, '-', 'Other') FROM products
3. Using suppliers, write a query to replace 'TBD' with 'Other' in the supplier_name column.
SELECT REPLACE(supplier_name, 'TBD', 'Other') FROM suppliers
4. Using employees, write a query to replace 'TBD' with 'Other' in the department column.
SELECT REPLACE(department, 'TBD', 'Other') FROM employees
5. Using reviews, write a query to replace '-' with 'Other' in the review_id column.
SELECT REPLACE(review_id, '-', 'Other') 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