Reliable pipelines

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.

  1. 1Be the human
  2. 2What the database sees
  3. 3TRIM and LOWER
  4. 4REPLACE
  5. 5LIKE and ILIKE
  6. 6CASE
  7. 7A mapping table
  8. 8Catch the new spellings
  9. 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.