Create sequence with order AAB, AA1, AA9, ABA-CodePudding

I am trying to create sequence that stored in db. So just only with one service call I should get new sequence by order like AAB. Next call should return AAC, next AAD.... AA9, ABA... I tried to create three number sequences 0<=first_seq<36, also like this second_seq, third_seq. I am using spring hibernate, postgresql.

CodePudding user response：

Try this.

static final String DIGITS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
static final int LENGTH = 3;
static int SEED = 0;

public static String nextSequence() {
    int size = DIGITS.length();
    char[] buffer = new char[LENGTH];
    for (int n =   SEED, i = LENGTH - 1; i >= 0; --i, n /= size)
        buffer[i] = DIGITS.charAt(n % size);
    return new String(buffer);
}

and

for (int i = 0; i < 8;   i)
    System.out.println(nextSequence());

output

AAB
AAC
AAD
AAE
AAF
AAG
AAH
AAI

CodePudding user response：

Well your question is ambiguous, you should indicate wanting a Java or a Postgres solution at a minimum. The following presents a fully generic Postgres solution given only the previous sequence and a string with the ordered set of digits. It creates 2 CTEs the first defines a the digits then the second 'builds' a working set of variables. Finally the main sql builds the next sequence. This is handled is a single statement, nested into a sql function. (see demo)

create or replace function strange_sequence_nextval(current_seq_in text)
     returns text 
    language sql
    immutable
as $$
   with avail_set (k_ent) as ( values('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789') )
      , parms_set (k_ent, k_len, l_pos, l_len, l_val) as 
        (select k_ent
              , length(k_ent) 
              , position(right(current_seq_in, 1) in (k_ent) )
              , length(current_seq_in)
              , left(current_seq_in, length(current_seq_in)-1)
           from avail_set 
        ) 
    select case when current_seq_in is  null then left(k_ent,1) 
                when l_pos = k_len           then concat( strange_sequence_nextval(l_val), left(k_ent,1)) 
                when l_len < 2               then substr( k_ent, l_pos 1, 1)
                else                              concat( l_val, substr( k_ent, l_pos 1, 1)) 
           end
      from parms_set;
$$;

NOTE: As a SQL function it can be extracted and run as an independent statement. You will just need to pass the current sequence as a parameter.