10.5 Practice: Clean Agent Names and Check Hub Codes

By Ravindra Bagale Last updated: 31 October 2026

Why. Real lists mix every text problem at once: spaces, capital letters, missing names and codes that do not match. Fixing them in one query builds a habit you will use daily.

What. You combine trim(), replace(), upper(), lower(), substr() and coalesce() in one SELECT. This only changes what the query shows; the stored data stays the same. If two cleaned names become equal, treat them as possible duplicates and check the agent_id before you merge anything.

How. Build a clean display name for every agent, then compare each tracking code with the hub code it should match.

Steps

Run the first example and read the clean_name column.
Notice that only the first letter becomes a capital; a full title case needs more work.
Run the second example to list shipments whose code does not match a real hub.
Write down the two problem shipments so that someone can fix the source data.

Examples

Example 1 · dataset: swiftship_logistics

SELECT agent_id,
       coalesce(
         upper(substr(trim(replace(raw_name, '  ', ' ')), 1, 1)) ||
         lower(substr(trim(replace(raw_name, '  ', ' ')), 2)),
         'Name missing') AS clean_name
FROM agents
ORDER BY agent_id;

Output

agent_id	clean_name
1	Asha patil
2	Rahul sharma
3	Fatima khan
4	Oliver brown
5	élodie martin
6	Name missing

Meaning. Extra spaces are gone, Fatima's double space became one, and each name starts with a capital letter. élodie stays small because upper() does not change é. Agent 6 shows Name missing instead of NULL.

Example 2 · dataset: swiftship_logistics

SELECT s.shipment_id,
       s.tracking_code,
       s.hub_id,
       h.hub_code
FROM shipments AS s
LEFT JOIN hubs AS h ON h.hub_id = s.hub_id
WHERE h.hub_code IS NULL
   OR substr(s.tracking_code, 1, 6) <> h.hub_code;

Output

shipment_id	tracking_code	hub_id	hub_code
1009	LHR-03-00222	9	NULL
1010	PNQ-01-00020	NULL	NULL

Meaning. Two shipments need checking. 1009 points to hub 9, which does not exist. 1010 has no hub yet, so hub_id is NULL. Every other code matches its hub. The LEFT JOIN used here is explained fully in chapter 15.

Ravindra Bagale's Tip

replace(raw_name, ' ', ' ') changes each double space into one space once. A long run of spaces may need the same replace() more than once, and tabs or line breaks are not touched at all. Check the result instead of assuming one call fixed every space.