5

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

Ramu Pedada
  • 51
  • 1
  • 1
  • 3
  • 1
    A number can't have leading 0s, that's a character so remove the `TO_NUMBER()` around your last statement. – Ben Sep 22 '14 at 07:45
  • 1
    When a column is of `number` data type, leading zeroes won't be stored. Store values as numbers and use either `lpad()`(use `to_char()` to avoid implicit data type conversion) or `to_char()` directly when you are displaying data. – Nick Krasnov Sep 22 '14 at 07:46

4 Answers4

10

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
5
select to_char(x,'00000') from dual;
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Giorgi Orvelashvili
  • 113
  • 1
  • 11
1

If you really want to store those numbers with preceeding zeroes then you must change the datatype to varchar2. Then you can apply to_char( artnr , 'fm00009') in the update statement. Of course, this might come with unintended consequences. Go for this solution at your own risk.

You might also consider creating a view that zero-paddes the number "on the fly" when you select from that view.

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
0

In my case the goal was calculate the sum of the values of the different currencies, but the issue was created by the data type of the field VALUE that is VARCHAR2(255 BYTE). I have found this solution, for handle the problem of the leading zero:

    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;
cosgiu83
  • 1
  • 1