How to add leading zero in a number in Oracle SQL query?

You could do it in two ways.

Method 1

Using LPAD.

For example,

SQL> WITH DATA(num) AS(  2  SELECT 540 FROM dual UNION ALL  3  SELECT 60 FROM dual UNION ALL  4  SELECT 2 FROM dual  5  )  6  SELECT num, lpad(num, 5, '0') num_pad FROM DATA;       NUM NUM_P---------- -----       540 00540        60 00060         2 00002SQL>

The WITH clause is only to build sample data for demo, in your actual query just do:

lpad(removal_count, 5, '0')

Remember, a number cannot have leading zeroes. The output of above query is a string and not a number.

Method 2

Using TO_CHAR and format model:

SQL> WITH DATA(num) AS(  2  SELECT 540 FROM dual UNION ALL  3  SELECT 60 FROM dual UNION ALL  4  SELECT 2 FROM dual  5  )  6  SELECT num, to_char(num, '00000') num_pad FROM DATA;       NUM NUM_PA---------- ------       540  00540        60  00060         2  00002SQL>

Update : To avoid the extra leading space which is used for minus sign, use FM in the TO_CHAR format:

Without FM:

SELECT TO_CHAR(1, '00000') num_pad,  LENGTH(TO_CHAR(1, '00000')) tot_lenFROM dual;NUM_PAD    TOT_LEN------- ---------- 00001           6 

With FM:

SELECT TO_CHAR(1, 'FM00000') num_pad,  LENGTH(TO_CHAR(1, 'FM00000')) tot_lenFROM dual;NUM_PAD    TOT_LEN------- ----------00001            5

Use the LPAD pl/sql function http://www.techonthenet.com/oracle/functions/lpad.php.

removal_count = 540 LPAD(TO_CHAR(removal_count), 5, '0');Result: '00540'

In sqlplus you can use col format:

SQL> select 540 aa, 540 bb from dual ;

    AA         BB

   540        540

SQL> col bb format 00000

SQL> /

    AA     BB

   540  00540

SQL>