lesson 16 Calculations & data quality · intermediate

CAST, text types & NULL arithmetic

Convert sensor values and calculate deviations with explicit units.

the idea

CAST converts a value to another type. Casting text to INTEGER reads the leading digits and stops at the first non-digit, so '02134-1207' becomes 2134 and leading zeros vanish. Any arithmetic or concatenation with NULL produces NULL; COALESCE supplies a fallback.

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

   See messy postal codes as stored. Run this. Some codes lack a leading zero, some include ZIP+4, and some are missing.

   step-1.sql Run

   sql›

   SELECT shipment_id, destination_postal_code
   FROM shipments
   WHERE destination_state = 'MA'
   LIMIT 15;

2. 2

   Try CAST and string concatenation. Run this. CAST changes type; concatenating with NULL yields NULL—COALESCE is what fixes display later.the pattern

   example.sql Run

   sql›

   SELECT
     CAST('02134' AS INTEGER) AS as_number,
     CAST(42 AS TEXT) || '!' AS as_text,
     'ZIP ' || NULL AS with_null;

common mistake

A ZIP code is an identifier, not a number. CAST(zip AS INTEGER) loses leading zeros and the ZIP+4 suffix.