Pro lesson Intermediate
Taming messy text
Match values that differ in case, spacing and spelling: LOWER, TRIM, LIKE and lookup tables.
10 min9 hands-on steps
In this lesson you will
- See how one shop spelled five ways splits every total
- Normalize text with TRIM, LOWER, REPLACE, LIKE and CASE
- Replace hand-written rules with a mapping table, and catch new spellings before they go missing
Words you'll meet
- Normalize
- Turn the different ways a value is written into one standard way.
- Pattern
- Text with wildcards, used with LIKE: % stands for any run of characters, _ for exactly one.
- CASE
- A rule inside a query: WHEN this is true THEN use that value, ELSE something else.
- Mapping table
- A small table that lists each known spelling next to its one correct value. Also called a lookup table.
The steps
Each step teaches one idea, then has you try it hands-on in your browser, and checks your work.
- 1Be the human
- 2What the database sees
- 3TRIM and LOWER
- 4REPLACE
- 5LIKE and ILIKE
- 6CASE
- 7A mapping table
- 8Catch the new spellings
- 9Challenge
This lesson is part of LearnDE Pro
$12 a month or $100 a year: every lesson, spaced-repetition flashcards, and Learn by teaching. Refund on request within 14 days.