Delhivery mid GLOBCharacter ClassesIS NULLWHERE

Return awb, hub and pincode for every shipment whose PIN code is NOT a valid Indian PIN code — valid means it matches GLOB '[1-9][0-9][0-9][0-9][0-9][0-9]' (exactly 6 digits, first digit 1-9). A shipment with no PIN code at all (pincode IS NULL) must also be included. Order by awb.

Delhivery · Shipment Data Quality — practice this real-world SQL scenario live in your browser.

📖 Story Delhivery · Shipment Data Quality

Delhivery's address-quality team wants a daily list of shipments whose PIN code can't be trusted, so the call centre can phone those customers before the parcel is sent to the wrong delivery centre. An Indian PIN code is exactly 6 digits and never starts with 0. The pincode column is TEXT because it's typed in by sellers, and the mistakes are real: a digit missing, a capital letter O typed in place of a zero, a space in the middle, a leading 0, and one order where the seller left the PIN code out entirely (pincode is NULL — 'never captured', which is the worst case of all, not a safe one).

🎯 Your Mission

📋 Table Structure

🗂 delhivery_shipments

awb TEXT DL1001

hub TEXT Bhiwandi

dest_city TEXT Mumbai

pincode TEXT 400001

transit_days INTEGER 2

⚡ Step-by-Step Walkthrough

Test every PIN code against LENGTH, LIKE and GLOB

query.sql

SELECT awb, pincode,
       LENGTH(pincode) AS len,
       pincode LIKE '______' AS like_6_chars,
       pincode GLOB '[1-9][0-9][0-9][0-9][0-9][0-9]' AS glob_valid
FROM delhivery_shipments
ORDER BY awb;

💡 Explanation

A LENGTH(pincode) <> 6 check would only catch DL1003 ('40001', 5 characters); '1100O1', '560 01' and '070001' are all exactly 6 characters long, so length alone says nothing about whether they are digits.
LIKE '______' (six underscores) matches any 6 characters, so it also returns 1 for those same three bad codes — LIKE has only % and _, with no way to say 'a digit here', and in SQLite it's also case-insensitive for ASCII letters.
GLOB uses character classes: [0-9] means one digit and [1-9] one digit that isn't 0, so glob_valid is 0 for the letter O, the space, the short code and the leading-0 '070001' — every kind of bad code caught by a single pattern.
DL1011's pincode is NULL, and all three tests return NULL for it — not 0 and not 1 — because comparing NULL with anything gives 'unknown'; keep that row in mind for the next step.

The trap: NOT GLOB silently skips the NULL PIN code

query.sql

SELECT awb, hub, pincode
FROM delhivery_shipments
WHERE pincode NOT GLOB '[1-9][0-9][0-9][0-9][0-9][0-9]'
ORDER BY awb;

💡 Explanation

Four rows come back — DL1003, DL1005, DL1009 and DL1014 — and the pattern is doing its job perfectly on every one of them; the bug is the row that isn't here.
For DL1011, pincode GLOB '...' is NULL, and NOT NULL is still NULL; WHERE keeps only rows where the condition is TRUE, so the shipment with no PIN code at all quietly drops out.
That's the most dangerous shipment in the table — a parcel with no PIN code can't be routed to any delivery centre — and it's the only one the call centre would never hear about.
The same thing happens with NOT LIKE, <> and NOT IN: any 'is this value bad?' test written as a negation treats NULL as neither good nor bad, and WHERE throws 'neither' away.

Fix it: check for NULL explicitly, then apply the pattern

query.sql

SELECT awb, hub, pincode
FROM delhivery_shipments
WHERE pincode IS NULL
   OR pincode NOT GLOB '[1-9][0-9][0-9][0-9][0-9][0-9]'
ORDER BY awb;

💡 Explanation

pincode IS NULL is always TRUE or FALSE, never unknown, so for DL1011 the OR becomes TRUE OR NULL, which is TRUE, and the row is kept.
Five shipments come back: DL1003 (5 digits), DL1005 (letter O), DL1009 (space), DL1011 (no PIN code) and DL1014 (starts with 0) — two each from the Bengaluru hub, and one each from Bhiwandi, Gurugram and Kolkata.
GLOB is case-sensitive and anchored — the whole string must match the pattern, so '5600011' (7 digits) or ' 560001' (leading space) would also fail it — which is exactly what you want from a strict format check.
GLOB uses * and ? as wildcards instead of LIKE's % and _, and adds [...] classes such as [0-9] or [^0-9] for 'not a digit'; it's SQLite-specific, so on PostgreSQL the same check would be written with a regular expression (pincode ~ '^[1-9][0-9]{5}$').

▶ Practice this live ← Browse all questions