SQLite function to format numbers with leading zeroes?

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).

Since 3.8.3 (2014) You can use printf as:

SELECT printf('%04d', product_number) AS product_number FROM table;

Change the 4 to however many digits you need. This would return 0001 for product_number 1.