How to Format Numbers with Leading Zeros in SQLite

by

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%