task TRIM / LOWER / SUBSTR / REPLACE · medium

Parse malformed product codes

Normalize raw_code by trimming ASCII spaces and uppercasing it. Keep only codes of the exact form HM-DDDD-AA, where DDDD is four ASCII digits representing 1–9999 and AA is two ASCII letters. Return import_id, integer item_number and uppercase region.

what to return

Columns

import_iditem_numberregion in this order

Rows

11 rows · in the order asked

Watch for

Reject NULL, 0000, extra characters, internal spaces and malformed delimiters. Order by import_id.

Region is any pair of ASCII letters, not a lookup against a country list.

Skill

Normalize and validate a fixed-format identifier before parsing it. Refresher: CAST, text types & NULL arithmetic →

expected result

Your query should return exactly these columns and values. Run it and compare.

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.