Unit and format inconsistencies

When the same thing is written different ways ('$300.00', '50 USD', grams and kilograms), convert everything to one format before you calculate.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

One unit, one format

SELECT product_id, CAST(REGEXP_REPLACE(price_text, '[^0-9.]', '', 'g') AS NUMERIC) AS price,
       CASE WHEN weight_unit = 'g' THEN weight / 1000 ELSE weight END AS weight_kg
FROM products

Result:

product_id	price	weight_kg
P2001	300.0	1.2
P2002	50.0	0.45
P2003	25.0	0.3
P2004	130.0	2.5
P2005	90.0	0.8

Step by step

REGEXP_REPLACE removes everything that isn't a digit or a dot: '$300.00' becomes '300.00'.
```
SELECT product_id, REGEXP_REPLACE(price_text, '[^0-9.]', '', 'g') AS price
FROM products;
```

CAST(... AS NUMERIC) so you can add and compare prices.

SELECT product_id, CAST(REGEXP_REPLACE(price_text, '[^0-9.]', '', 'g') AS NUMERIC) AS price
FROM products;

CASE turns grams into kilograms, so every weight is in kg.

SELECT product_id, CAST(REGEXP_REPLACE(price_text, '[^0-9.]', '', 'g') AS NUMERIC) AS price,
       CASE WHEN weight_unit = 'g' THEN weight / 1000 ELSE weight END AS weight_kg
FROM products;

Practice

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

1. Show product_id and price_text with everything except digits and dots removed, as price.

SELECT product_id, REGEXP_REPLACE(price_text, '[^0-9.]', '', 'g') AS price FROM products

2. Show product_id and the weight in kilograms as weight_kg: divide by 1000 when weight_unit is 'g'.

SELECT product_id, CASE WHEN weight_unit = 'g' THEN weight / 1000 ELSE weight END AS weight_kg FROM products

3. Show product_id and the price as a number, as price: strip everything except digits and dots, then CAST it to NUMERIC.

SELECT product_id, CAST(REGEXP_REPLACE(price_text, '[^0-9.]', '', 'g') AS NUMERIC) AS price FROM products

4. Show the total of all prices as total_price, after cleaning them into numbers.

SELECT SUM(CAST(REGEXP_REPLACE(price_text, '[^0-9.]', '', 'g') AS NUMERIC)) AS total_price FROM products

5. Show product_id, the cleaned price as price, and the weight in kg as weight_kg.

SELECT product_id, CAST(REGEXP_REPLACE(price_text, '[^0-9.]', '', 'g') AS NUMERIC) AS price, CASE WHEN weight_unit = 'g' THEN weight / 1000 ELSE weight END AS weight_kg FROM products

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