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

    How to increment a varchar field sequentially

    3383498

      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

        • 1. Re: How to increment a varchar field sequentially
          John_K

          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

            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;