Sql - SUBSTR, INSTR and REPLACE
Code Life
By Pierre Marsaa
SUBSTR: a piece of text
ref_code packs three pieces of information: region, year, number — 'AZ-2023-0017'. SUBSTR(text, start, length) cuts a piece out. Positions start at 1:
↗️
Loading editor...
↶
↷
Without a length, SUBSTR goes to the end of the text.
Once extracted, a piece is a value like any other — you can group by it:
↗️
Loading editor...
↶
↷
Counting from the end
A negative start counts from the end: SUBSTR(ref_code, -4) is the last 4 characters, '0017'. Handy when the beginning varies in length.
Verify