1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Leading 0's trimmed during addition

Discussion in 'SQL PL/SQL' started by arunshankar.c, Mar 15, 2012.

  1. arunshankar.c

    arunshankar.c Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Hi,

    when adding a varchar value and number, I end up getting a situation where the Leading 0's associated with the varchar variable is actually trimmed off. Say for eg : In below code, i have declared v_start_value_varchar as a varchar2 variable and j is the index where the v_difference_value will have values between 0 - 9. When the content of v_start_value_varchar is say : 009001 and I add value of j on this, I end up getting Output as 9002 instead of 009002. Please suggest if there is any way to retain the leading 0's, and yes I cannot hard code the leading 0's since the Leading 0's is not constant.

    Code (Text):

    FOR j IN 0..v_difference_value LOOP
        v_start_value_varchar := v_start_value_varchar + j;
    END LOOP;
     

    Thanks,
    Arun
     
  2. tj.abrahamsen

    tj.abrahamsen Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Utah
    Hello Arun -

    I am not totally sure if this answers your question, but you have to decide if you want to use numbers or characters. In your case you are using a "+ j", where j is a number. And, when you are using "+" you are telling PL/SQL that you want to use regular math...

    What you might want to do is to treat the whole as characters...something like this:

    Code (Text):

    SQL> set serveroutput on
    SQL>
    SQL> DECLARE
      2     v_start_value_varchar   VARCHAR2(2000);
      3      v_difference_value     INTEGER;
      4  BEGIN
      5      v_start_value_varchar := '009001';
      6     v_difference_value := 9;
      7  
      8      FOR j IN 0..v_difference_value LOOP
      9         v_start_value_varchar := v_start_value_varchar || TO_CHAR(j);
     10          dbms_output.put_line(v_start_value_varchar);
     11     END LOOP;
     12  END;
     13  /
     
    0090010
    00900101
    009001012
    0090010123
    00900101234
    009001012345
    0090010123456
    00900101234567
    009001012345678
    0090010123456789
     
    PL/SQL procedure successfully completed
     
    Hope this helped.

    ~ TJ
     
    Sadik likes this.