Below are examples of adding a leading zero to a number in SQL, using various DBMSs.

Oracle

Oracle has a TO_CHAR(number) function that allows us to add leading zeros to a number. It returns its result as a string in the specified format.

SELECT TO_CHAR(7, '000')
FROM DUAL;

Result:

007

The 0 format element is what outputs the leading zeros. If we didn’t want leading zeros, we could use 9.

Here’s a comparison between 0 and 9 to demonstrate what I mean:

SELECT 
    TO_CHAR(1234.456, '000G000D00') AS "0",
    TO_CHAR(1234.456, '999G999D99') AS "9"
FROM DUAL;

Result:

             0              9 
______________ ______________ 
 001,234.46       1,234.46    

The G is for the group separator and the D is for the decimal separator. See this list of number format elements for a full list.

Oracle also has an LPAD() function that enables us to pad a number with leading zeros (or any other character). See 2 Ways to Format a Number with Leading Zeros in Oracle for an example.

PostgreSQL

PostgreSQL also has a TO_CHAR() function, and it works like Oracle’s function of the same name:

SELECT TO_CHAR(7, '000');

Result:

007

And here’s a comparison between 0 and 9:

SELECT 
    TO_CHAR(1234.456, '000G000D00') AS "0",
    TO_CHAR(1234.456, '999G999D99') AS "9";

Result:

      0      |      9      
-------------+-------------
  001,234.46 |    1,234.46

See Template Patterns & Modifiers for Numeric Formatting in Postgres for a full list of template patterns that can be used with this function in PostgreSQL.

Like Oracle, PostgreSQL also has an LPAD() function that enables us to pad numbers with leading zeros. See 2 Ways to Add Leading Zeros in PostgreSQL for an example.

MySQL

MySQL has an LPAD() function that allows us to pad the left part of a string or number with our chosen character or series of characters. Therefore, we can use it to pad our number with zeros:

SELECT LPAD(7, 3, 0);

Result:

007

Fortunately, we can pass the number as a numeric type, so there’s no need to convert it to a string first.

See How to Add Leading Zeros to a Number in MySQL for more.

MariaDB

Like MySQL, MariaDB also has an LPAD() function that allows us to pad the left part of a string or number with our chosen character or series of characters:

SELECT LPAD(7, 3, 0);

Result:

007

And like with MySQL, we can pass the number as a numeric type, so there’s no need to convert it to a string first.

See How to Pad a Number with Leading Zeros in MariaDB for more.

SQL Server

SQL Server has a FORMAT() function that allows us to format numbers using a format string:

SELECT FORMAT(7, '000');

Result:

007

Here it is with a group separator and decimal separator:

SELECT FORMAT(1234, '0,000.00');

Result:

1,234.00

See Add Leading & Trailing Zeros in SQL Server for more.

SQLite

SQLite has a PRINTF() function that can be used to add leading zeros to a number:

SELECT PRINTF('%03d', 7);

Result:

007

See How to Format Numbers with Leading Zeros in SQLite for an explanation.

Update: 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.

So the above example can be changed to this:

SELECT FORMAT('%03d', 7);

Result:

007