SQLite function to format numbers with leading zeroes?

Question

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?

Answer 1

This can be accomplished with a little bit of magic using string concatenation and the substr function. Long story short, here's what you want:

select substr('0000000000'||'1234', -10, 10)

An explanation follows.

First, know that SQLite's string concatenation operator is ||.

We'll start by taking a string that is as long as the value we want to return. For example, if you want to return a number that is always 10 characters long and have it be left padded with 0s if it is shorter, then start with a string of ten 0s. To that, we will concatenate the number you want to return. In our example, that is '1234'. So far, we have the part that looks like this: '0000000000'||'1234'

Then, pass that whole thing into the substr function. According to the documentation, here's how the substr function works:

The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. ... If Y is negative then the first character of the substring is found by counting from the right rather than the left.

So if you want the string to be 10 characters long, pass -10 as the Y parameter (to start counting from the right, 10 characters back) and pass 10 as the Z parameter (to take 10 characters total).