In SQLite, we can use the PRINTF()
function or FORMAT()
function to format numbers with leading zeros.
Example
SELECT PRINTF('%03d', 7);
Result:
007
The percent sign (%
) indicates that a substitution follows.
The 0
indicates that SQLite should prepend as many 0
characters to numeric substitutions as necessary to expand the value out to the specified width.
The d
indicates that the argument is a signed integer that is displayed in decimal (as opposed to hexadecimal, octal, etc).
So if we wanted more leading zeros, we can increase the 3
to a larger number:
SELECT PRINTF('%09d', 7);
Result:
000000007
For integers, we can include a comma for the group separator:
SELECT PRINTF('%0,9d', 72345);
Result:
000,072,345
The FORMAT()
Function
SQLite 3.38.0 (released 22 Feb 2022) renamed the PRINTF()
function to FORMAT()
. The original PRINTF()
name is retained as an alias for backwards compatibility.
Therefore, the first example above can be changed to this:
SELECT FORMAT('%2d%%', 17);
Result:
17%