lesson 33 Dates, strings & subqueries · intermediate

Clean strings & cast values

Clean text and convert values to the types you need.

the idea

TRIM removes outside spaces, LOWER normalizes case, SUBSTR extracts a portion, and CAST makes a conversion explicit. Keep NULL visible when the source is missing. SQLite uses SUBSTR; many engines also provide SUBSTRING.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT id,LOWER(TRIM(email)) AS clean_email
FROM members;

Watch for: Normalizing email is useful for analysis, but changing a real identity field needs business rules.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   See messy emails before cleaning. Whitespace and mixed case make the same address look different. Cleaning must happen before you compare or group.

   step-1.sql Run

   sql›

   SELECT id, email
   FROM members
   ORDER BY id
   LIMIT 8;

2. 2

   Normalize with TRIM and LOWER. TRIM removes surrounding spaces; LOWER folds case. Together they produce a stable clean_email.

   step-2.sql Run

   sql›

   SELECT id, LOWER(TRIM(email)) AS clean_email
   FROM members;