Write a query to return every row in sites with site_code split on dashes into country, city and seq, as in IN-BLR-01.
Uppercase country and city; preserve the stored sequence text, including leading zeros. Return NULL for any missing or empty part. Keep rows with incomplete, empty or NULL codes.
Ordering: Sort by site_id in ascending order.
Returnsite_idcountrycityseq
About this problem
You practise pulling fixed pieces out of a code that is not always complete, which is the normal state of any column people type by hand. sites.site_code should be IN-BLR-01, but some rows have one or two parts, one is an empty string and one is NULL, and you return country, city and seq with every absent piece shown as NULL. The skill is knowing what your extraction function returns when the piece is not there, because a wrong answer here is invisible.
The query almost everyone writes is three calls to split_part, and on the two complete codes it is perfect. The problem is site 3: asking for the second piece of 'SG' returns an empty string rather than NULL, because split_part has a result to give you, it is just empty. In a results grid an empty cell and a NULL cell look the same, so the mistake survives review and then shows up downstream – count(city) counts the empty strings, a GROUP BY city gets a group with a blank label, and “sites with no city” reports zero. The other temptation is to read the parts by position with left() and substring(), which works on every code that is the right shape and quietly returns rubbish on the ones that are not: for IN-DEL, right(site_code, 2) happily hands back EL.
Read the lesson: String Functions (P12) →