runtimetask TRIM / LOWER / SUBSTR / REPLACE · medium
Parse tracking numbers
For packages in shipment 1–200, return package_id, carrier_prefix (first two characters), serial (the nine digits after the prefix) and check_letter (the final letter) of the normalized tracking number (trimmed, spaces removed, upper case).
what to return
- Columns
package_idcarrier_prefixserialcheck_letterin this order- Rows
- 289 rows · any order
- Skill
- Extract parts of a structured identifier. Refresher: Clean strings & cast values →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result289 rows · any order
| package_id | carrier_prefix | serial | check_letter |
|---|---|---|---|
| 1 | RT | 100007919 | B |
| 2 | RT | 100015838 | C |
| 3 | RT | 100023757 | D |
first 3 of 289 rows
data you can query
Logistics dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
packages8 columns · 11,505 rows
| package_idnumber · unique id | shipment_idnumber · links to shipments | tracking_numbertext | package_typetext | weight_kgdecimal | length_cmnumber | width_cmnumber | height_cmnumber |
|---|---|---|---|---|---|---|---|
| 1 | 1 | RT100007919B | box | 2.85 | 52 | 15 | 30 |
| 2 | 1 | RT100015838C | box | 1.1 | 69 | 45 | 24 |
| 3 | 2 | RT10 0023 757D | envelope | 0.49 | 32 | 24 | 2 |
| 4 | 3 | RT100031676E | box | 2.93 | 51 | 35 | 5 |
| 5 | 4 | RT100039595F | envelope | 0.38 | 32 | 24 | 3 |
| 6 | 5 | RT100047514G | box | 1.9 | 30 | 27 | 25 |
- package_idnumber · unique id
- 1
- shipment_idnumber · links to shipments
- 1
- tracking_numbertext
- RT100007919B
- package_typetext
- box
- weight_kgdecimal
- 2.85
- length_cmnumber
- 52
- width_cmnumber
- 15
- height_cmnumber
- 30
- package_idnumber · unique id
- 2
- shipment_idnumber · links to shipments
- 1
- tracking_numbertext
- RT100015838C
- package_typetext
- box
- weight_kgdecimal
- 1.1
- length_cmnumber
- 69
- width_cmnumber
- 45
- height_cmnumber
- 24
- package_idnumber · unique id
- 3
- shipment_idnumber · links to shipments
- 2
- tracking_numbertext
- RT10 0023 757D
- package_typetext
- envelope
- weight_kgdecimal
- 0.49
- length_cmnumber
- 32
- width_cmnumber
- 24
- height_cmnumber
- 2
- package_idnumber · unique id
- 4
- shipment_idnumber · links to shipments
- 3
- tracking_numbertext
- RT100031676E
- package_typetext
- box
- weight_kgdecimal
- 2.93
- length_cmnumber
- 51
- width_cmnumber
- 35
- height_cmnumber
- 5
- package_idnumber · unique id
- 5
- shipment_idnumber · links to shipments
- 4
- tracking_numbertext
- RT100039595F
- package_typetext
- envelope
- weight_kgdecimal
- 0.38
- length_cmnumber
- 32
- width_cmnumber
- 24
- height_cmnumber
- 3
- package_idnumber · unique id
- 6
- shipment_idnumber · links to shipments
- 5
- tracking_numbertext
- RT100047514G
- package_typetext
- box
- weight_kgdecimal
- 1.9
- length_cmnumber
- 30
- width_cmnumber
- 27
- height_cmnumber
- 25
first 6 of 11,505 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.