10.4 How to Cut Text with substr()

Why. Codes often carry meaning in fixed places. The first three letters of a tracking code can tell you the city, and the end can be a serial number.

What. substr(text, start, length) returns a piece of the text. The first character is position 1, not 0. If you leave out the length, you get everything to the end. In SQLite a negative start counts from the right, so substr(code, -5) gives the last five characters. Other databases treat a negative start differently, so check before you move the query.

How. Cut each tracking code into a city code, a hub code part and a serial number.

SELECT tracking_code,
       substr(tracking_code, 1, 3) AS city_code,
       substr(tracking_code, 1, 6) AS hub_part,
       substr(tracking_code, -5)   AS serial
FROM shipments
WHERE shipment_id IN (1001, 1006);