lesson 33 Dates, strings & subqueries · intermediate

Clean strings & cast values

Clean text and convert values to the types you need.

the idea

TRIM removes outer spaces, UPPER and LOWER normalize case, REPLACE substitutes text, instr finds a position and substr extracts part of a string. Clean a value in a defined order of steps, and keep NULL as NULL instead of inventing a value.

Dataset: Logistics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Spot tracking numbers that need cleaning. Run this. Compare raw tracking_number to trimmed, space-stripped, uppercased form.the pattern

   example.sql Run

   sql›

   SELECT
     tracking_number,
     UPPER(REPLACE(TRIM(tracking_number), ' ', '')) AS clean
   FROM packages
   WHERE tracking_number <> UPPER(REPLACE(TRIM(tracking_number), ' ', ''))
   LIMIT 10;

2. 2

   Take text before a hyphen. Run this. instr locates '-'; substr keeps the ZIP portion before the suffix.

   step-2.sql Run

   sql›

   SELECT
     destination_postal_code,
     instr(destination_postal_code, '-') AS hyphen_at,
     substr(destination_postal_code, 1, instr(destination_postal_code, '-') - 1) AS before_hyphen
   FROM shipments
   WHERE destination_postal_code LIKE '%-%'
   LIMIT 10;

common mistake

Padding with zeros before removing a ZIP+4 suffix pads the wrong text. Split at the hyphen first, then pad.