When using Oracle Database to format a number to have leading zeros, we need to convert it to a string and format it accordingly.

You can use the TO_CHAR(number) function to format numbers with leading zeros.

And perhaps a lesser known fact, is that you can also use the LPAD() function to pad a number with leading zeros.

The TO_CHAR() Function

When using the TO_CHAR(number) function, use the 0 format element to include leading and/or trailing zeros.

Example:

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

Result:

007

Here it is when compared to the 9 format element:

SELECT 
    TO_CHAR(7, 'fm999') AS "9",
    TO_CHAR(7, 'fm000') AS "0"
FROM DUAL;

Result:

   9      0 
____ ______ 
7    007   

The number of leading zeros are determined by the number of 0 format elements and the number of digits in the number:

SELECT 
    TO_CHAR(77, 'fm0') AS "r1",
    TO_CHAR(77, 'fm00000') AS "r2",
    TO_CHAR(777, 'fm00000') AS "r3",
    TO_CHAR(7777, 'fm00000') AS "r4",
    TO_CHAR(77777, 'fm00000') AS "r5"
FROM DUAL;

Result:

   r1       r2       r3       r4       r5 
_____ ________ ________ ________ ________ 
##    00077    00777    07777    77777   

The fm format modifier suppresses any leading and/or trailing padding that might be included in the result. For example, when the number is negative, a minus sign is prefixed. But when the number is positive, no sign is included, and a space appears where the plus sign would have been.

Here’s an example to demonstrate this:

SELECT 
    TO_CHAR(-7, '000') AS "Minus Sign",
    TO_CHAR(7, '000') AS "Padded",
    TO_CHAR(7, 'fm000') AS "Not Padded"
FROM DUAL;

Result:

   Minus Sign    Padded    Not Padded 
_____________ _________ _____________ 
-007           007      007          

The LPAD() Function

We can alternatively use the LPAD() function to format numbers with leading zeros.

Example:

SELECT LPAD(7, 3, '0')
FROM DUAL;

Result:

007

Another example:

SELECT 
    LPAD(77, 1, '0') AS "r1",
    LPAD(77, 5, '0') AS "r2",
    LPAD(777, 5, '0') AS "r3",
    LPAD(7777, 5, '0') AS "r4",
    LPAD(77777, 5, '0') AS "r5"
FROM DUAL;

Result:

   r1       r2       r3       r4       r5 
_____ ________ ________ ________ ________ 
7     00077    00777    07777    77777   

One of the benefits that LPAD() has over the previous method is that we can prefix the result other characters – it doesn’t need to be a leading zero.

SELECT 
    LPAD(77, 1, '.') AS "r1",
    LPAD(77, 5, '.') AS "r2",
    LPAD(777, 5, '.') AS "r3",
    LPAD(7777, 5, '.') AS "r4",
    LPAD(77777, 5, '.') AS "r5"
FROM DUAL;

Result:

   r1       r2       r3       r4       r5 
_____ ________ ________ ________ ________ 
7     ...77    ..777    .7777    77777