How to display the leading zero in a row of oracles

Question

My oracle column (artnr) contains a length of 1, which has a type number (9). I want to update the number as follows:

Example:

If the number is 0, then it must be 00000; If the number is 1, then it must be 00001; If the number is 12, it must be 00012

Remember: here 00000,0000 and 00012 have numeric data types

Below are the methods that I 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 to strings

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

Still failed because to_number does not display the leading zero. He will consider only from the first issue

Someone, could you please offer me something that will resolve this scenario.

sql is preferred over pl / sql solution

Answer 1

0, 00000. 1, be 00001 12, 00012

: 00000,0000 00012

-, . , NUMBER, NUMBER. , LPAD . .

. LPAD, , .

 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_CHAR LPAD.

Answer 2

select to_char(x,'00000') from dual;

Answer 3

, varchar2. to_char( artnr , 'fm00009') . , . .

, " " .

Answer 4

In my case, the goal was to calculate the sum of the values ​​of different currencies, but the problem was created by the data type of the VALUE field, which is VARCHAR2 (255 BYTE). I found this solution to deal with the initial zero problem:

    SELECT ID_OUT
          , CASE WHEN REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.') LIKE '-.%' THEN REPLACE(REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.'), '-.', '-0.')
                 WHEN REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.') LIKE '.%' THEN REPLACE(REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.'), '.', '0.')
                 ELSE REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.') 
            END AS VALORE
         , LOB
         , 'TOTAL' CURRENCY
         , COUNTRY
    FROM QRT_OUT_DATI
    WHERE (CURRENCY != 'Total' AND CURRENCY != 'TOTAL')
    GROUP BY ID_OUT, LOB, COUNTRY, CURRENCY 
    ORDER BY LOB;