Lesson 8 of 13 · 20 min
Standardizing inconsistent values
By the end of this lesson you can normalise a messy column into canonical values, map raw values to reporting categories with CASE or a VALUES mapping CTE, and detect values your mapping does not cover.
Standardizing is a two-step job:
- Normalise the surface: same case, no stray spaces, same punctuation. This turns spelling variants of one value into one string:
upper(trim(coupon_code)),initcap(trim(city)). - Map the normalised value to the category the business reports on: coupon
RAMADAN15→ campaign Ramadan, city Khobar → zone East. Normalisation alone cannot do this: no string function knows that NATIONALDAY93 and NATIONALDAY95 are the same campaign.
The golden rule is to group by the standardized expression, not by the raw column it came from.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons