How to display the leading zero's in a number of oracle

Question

I have an oracle column(artnr) contains a length of 1 which is of type number(9). I want to update the number as following...

Example :

If number is 0 then it should be 00000 If number is 1 then it should be 00001 If number is 12 the it should be 00012

Remember : here 00000,0000, and 00012 are of number datatypes

The following are the methods I have tried but failed..

UPDATE pitb.toestel b
   SET b.artnr = LPAD (b.artnr, 5, 0)
 WHERE b.idinventaris = 403743;

Failed because Lpad can only be applied on strings

UPDATE pitb.toestel b
   SET b.artnr = TO_NUMBER (TO_CHAR (artnr, '00009'), '00009')
 WHERE b.idinventaris = 403743;

Still failed, because to_number will not display the leading zero's. It will only consider from first number

Anyone, could you please suggest me something which will solve this scenario..

sql is preferrable than pl/sql solution

Answer 1

If number is 0 then it should be 00000 If number is 1 then it should be 00001 If number is 12 the it should be 00012

Remember : here 00000,0000, and 00012 are of number datatypes

Firstly, Numbers don't have leading zero's. So, when you store the NUMBER values, you let them behave like NUMBERs. it is only when you want to display them, you can use LPAD and add the leading zeroes. Which conevrts the number to a string with leading zeroes.

So, no need to update the table. Use LPAD to display them the way you want.

 SQL> WITH DATA AS
  2    ( SELECT 1 ID FROM DUAL UNION ALL
  3      SELECT 11 ID FROM DUAL
  4    )
  5  SELECT
  6     LPAD(ID,5, 0) id
  7  FROM DATA
  8  /

ID
-----
00001
00011

To avoid, implicit data type conversion, use TO_CHAR before applying LPAD.