I’ve got an SQLite database that will need to contain products. Those products have a fixed-format composite product number, consisting of category, group and product number (cccccc.gg.ppp). Every time a new product is inserted, the new product number should be built using the category and group numbers, followed by the highest product number in that group incremented with one.
To that end, I was thinking of storing all three numbers in separate fields, and use a view to dynamically assemble the product number. For that to work, I would need to add leading zeroes, since the product number’s format is fixed.
However, I can’t seem to find a built-in SQLite function which would let me do this... :-(
I’d rather not write a custom function, since we might have to share the database with other developers, for them to read data from; and I have no idea what platform or language they’re going to use.
Can this be done? Or will we have to find a different solution?