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

Question

I am retrieving a column named removal_count in my query using COUNT() function. In result set the datatype of removal_count is BIGDECIMAL. I want to convert number into five digits. SO if value is less than five digits then it should be represented with leading zero's.

e.g 1) If removal count is 540 then display 00540
2) If removal count is 60 then display 00060

If the removal count is integer/string value then I can add leading zero's using java expression :

--if removal_count is integer-- String.format("%05d",removal_count)  --if removal_count is string-- ("00000"+removal_count).subString(removal_count.length()) 

Can we convert removal_count into string or integer ( from big decimal) so that I can use given java expression? Or else is there any way to add leading zero's in query itself?

Answer 1

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 00002  SQL> 

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  00002  SQL> 

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_len FROM dual;  NUM_PAD    TOT_LEN ------- ----------  00001           6  

With FM:

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