2 Replies Latest reply on Oct 10, 2019 12:14 PM by dmillerp

    How to increment a varchar field sequentially

    3383498
    3383498 Oct 10, 2019 3:48 AM

      Hello gurus,

       

      I have a column which has a value ASFDSFS00001

       

      I need to increment the last digit to make it to the new value as ASFDSFS00002 then ASFDSFS00003 onwards

       

      How to achieve this by extracting the last digit and increment the value by i.e

       

      ASFDSFS00001

       

      ASFDSFS00002

       

      ASFDSFS00003

      • 32 Views
      • Tags:
        • 1. Re: How to increment a varchar field sequentially
          John_K
          John_K Oct 10, 2019 7:21 AM (in response to 3383498)

          Is this an E-Business Suite question? If so, in what area? Is this a one-off update of existing data or are you looking to assign sequential numbering on a process going forwards? Can you post some sample data including table creation scripts (just a simple example).

          • 2. Re: How to increment a varchar field sequentially
            dmillerp
            dmillerp Oct 10, 2019 12:14 PM (in response to 3383498)

            Not sure why you would do this and not just use a sequence.  But here's my psuedo code shot at it: 

             

            DECLARE

               x                             VARCHAR2 (2000) := 'AFSDSFS00001';

               y                             NUMBER := 0;

            BEGIN

               WHILE y < 1000

               LOOP

                --  x := SUBSTR (x, 1,7 ) || lpad(TO_CHAR (TO_NUMBER (SUBSTR (x,8)) + 1),8-length(TO_CHAR (TO_NUMBER (SUBSTR (x,8)) + 1)),'0');

                x := SUBSTR (x, 1,7 ) || lpad(TO_CHAR (TO_NUMBER (SUBSTR (x,8)) + 1),8,'0');

                  DBMS_OUTPUT.put_line ('x is : ' || x);

                  y := y + 1;

               END LOOP;

            END;