By the end of this lesson you can filter inconsistently typed text with lower(), upper() and trim(), filter rows on calculated values such as line totals and discount percentages, and avoid the pitfalls of text-that-looks-like-numbers and floating-point equality.
Why it matters: Real data is typed by people and stored by many systems: 'Dammam ', 'biz5', phone numbers as text, discounts stored per line. Analysts who clean values inside the WHERE clause, and calculate the number the business actually means, produce counts that match finance's.
Dataset: Wadi Market online store. Examples run in the editor on the right.
This last lesson of the track applies what you know to the kind of data you'll meet at work. There are two themes: text that isn't consistent, and numbers that have to be calculated before you can filter on them.
Normalise text before comparing it
Wadi Market's customers typed their own city, so the same city can be stored as 'Dammam', 'Dammam ' (trailing space) or 'DAMMAM'. city = 'Dammam' misses the variants. The fix is to normalise both sides of the comparison with text functions:
trim(x)removes spaces at the start and end (not in the middle).lower(x)/upper(x)convert to one case.
WHERE lower(trim(city)) = 'dammam' compares the cleaned value with a cleaned constant, so every variant matches. The same works for coupon codes: upper(trim(coupon_code)) = 'BIZ5'.
SELECT customer_id, first_name, city
FROM customers
WHERE lower(trim(city)) = 'khobar';Besides the many customers stored as 'Khobar', this also returns the ones stored with a leading or trailing space. Change the WHERE to city = 'Khobar' and those messy rows disappear: a small difference in a count that someone will eventually question.
SELECT DISTINCT coupon_code,
upper(trim(coupon_code)) AS clean_code
FROM orders
WHERE upper(trim(coupon_code)) = 'WELCOME10';Each stored spelling appears once next to its cleaned version. Every variant (lowercase, trailing space) maps to the same clean_code, which is why the filter catches them all.
Text that looks like numbers
Phone numbers, SKUs and postal codes contain digits but are text, and that's correct: you never add phone numbers, and leading zeros or a leading + must be kept. Filter them as text, usually with LIKE on a prefix: phone LIKE '+96655%'. The trap is the opposite case: real quantities stored as text compare alphabetically ('100' < '9'), so convert them with value::numeric before comparing.
Filtering on a calculated number
WHERE can filter on any expression, not just a column. Wadi Market doesn't store the value of an order line; it stores quantity, unit_price and discount_amount, where discount_amount is the discount for the whole line, not per unit. So the line value is quantity * unit_price - discount_amount, and that whole expression can go in WHERE (repeated, because WHERE can't see the SELECT alias).
SELECT order_id, line_no, quantity, unit_price, discount_amount,
quantity * unit_price - discount_amount AS line_value
FROM order_items
WHERE quantity * unit_price - discount_amount > 20000;Only a small number of lines out of thousands pass, and almost all of them are bulk purchases by business customers (dozens of units of one product). The inputs are shown next to line_value so you can check one row by hand.
Percentages without division
"Lines discounted by at least 15%" means discount_amount / (quantity * unit_price) >= 0.15. Multiplying both sides by the gross value gives discount_amount >= 0.15 * quantity * unit_price, which is the same test but can never divide by zero and keeps integer division out of the picture. Mind the units: 15% is 0.15, not 15.
Exact and approximate decimals
Money columns use numeric, which stores decimals exactly: 0.1 + 0.2 = 0.3 is true. Columns of type real or double precision (float8) store binary approximations, and there 0.1 + 0.2 = 0.3 is false. Never test floating-point values with =; compare within a tolerance, abs(a - b) < 0.0001, or round both sides.
SELECT 0.1 + 0.2 = 0.3 AS numeric_equal,
0.1::float8 + 0.2::float8 = 0.3::float8 AS float_equal,
abs(0.1::float8 + 0.2::float8 - 0.3::float8) < 0.0001 AS float_close_enough;The first column is true: numeric decimals are exact. The second is false because 0.1 and 0.2 have no exact binary representation. The third shows the safe way to compare approximate values: within a tolerance.