Where is the "list of Heads"? Is it in a normalized table or in a single "cell" of a table or in a single variable. If either of the latter two, how is it stored? As a CSV, TSV, Fixed Width multi-entry or what?

As a bit of a sidebar and although I know you probably can't change it, I think it a bit insane to store numbers only values that need to be incremented (and, therefor, calculated) in an NCHAR column for several reasons. NCHAR usually makes no difference on numeric values, requires some special handling to increment, and wastes space because it requires 2 bytes per character. Your NCHAR(12) definition of this column occupies 24 bytes as opposed to just 8 bytes that would be used by a BIGINT.

Last but not least, your SIR Code appears to have a prefix and a sequence as a suffix. That also means that you have to use substring to split the 2 component parts out of the code for reporting. It would be a far better thing to store the 2 parts in separate columns and use a persisted computed column to put them together for display or reporting purposes.

If you can answer my questions as to "where" the "list of heads" is and what format it's in that I've identified in the first paragraph of this response, I'm positive that someone will be able to show you a relatively easy method to satisfy your requirements even if they, too, don't agree with the structure of the table.