Need SQL function for incremental sequence

Question

Can any one provide an SQL function which input string and output the next incremental string as per the following sequence?

000000
000001
.
.
000009
00000A
.
.
00000Z
.
.
.
000010
000011
.
.
000019
00001A
.
.
zzzzzz

That sequence can be produced by encoding an underlying incrementing integer to a base36 string see http://stackoverflow.com/questions/2568668/base-36-to-base-10-conversion-using-sql-only — Alex K., Feb 16 '12 at 11:20
@AlexK: It's not clear whether the OP wants base 36 - the final string in the sequence is zzzzzz, not ZZZZZZ, which implies that the OP may want base 62, or something else entirely. — , Feb 16 '12 at 13:33
@user674961: Can you clarify how you want the sequence to run? Is it to be 0..9A..Z, or 0..9A..Za..z, or something else? — , Feb 16 '12 at 13:36

score 0 · Answer 1 · answered Mar 22 '12 at 21:58

If you are able to use a PL/SQL function, you could try something like the function, next_step, in this block, which increments over an arbitrary numeral set.

DECLARE
    i integer;
    seqnum varchar2(20) := '00';
    ----------------------------------------
    function next_step(seq_num varchar2) return varchar2 is
        digits varchar2(20) := '012ABC';
        last_digit   varchar2(1) := substr(seq_num, length(seq_num), 1);
        other_digits varchar2(20) := substr(seq_num, 1, length(seq_num) -1);
        -- value of last digit
        last_digit_value number(5) := instr(digits, last_digit) - 1;
    BEGIN
        if seq_num is null then
           return substr(digits, 2, 1);
        end if;
        -- increment the digit; roll if needed.
        last_digit_value := last_digit_value + 1;
        -- "digits||digits" makes the roll easy.
        last_digit := substr(digits||digits, last_digit_value + 1, 1);
        if last_digit_value >= length(digits) then
          -- roll over
          other_digits := next_step(other_digits);
        end if;
        return other_digits||last_digit;
    END next_step;
    ----------------------------------------
BEGIN
    dbms_output.enable(null);
    dbms_output.put_line(seqnum);
    for i in 1 .. 50 loop
        seqnum := next_step(seqnum);
        dbms_output.put_line(' -> '||seqnum);
    end loop;
END;
/

It yields the following:

00
 -> 01
 -> 02
 -> 0A
 -> 0B
 -> 0C
 -> 10
 -> 11
 -> 12
 -> 1A
 -> 1B
 -> 1C
 -> 20
 -> 21
 -> 22
...
 -> C2
 -> CA
 -> CB
 -> CC
 -> 100
 -> 101
 -> 102
 ...
 -> 121
 -> 122

Need SQL function for incremental sequence

1 Answers1