skip to Main Content

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.

3

Answers


  1. 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
    
    Login or Signup to reply.
  2. 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.


    EDIT: Reply to Vérace. I did not get the generate the entire sequence because as stated Next call should return AAC, next (call) AAD …. Further the sequence from ... AA9, ABA... has no ending. But without reworking the function you could use it within a recursive query.

    with recursive seq_gen(seq, gen_no, stop_at) as
         ( select 'AAA', 1, 100         -- or any other random point
           union all 
           select strange_sequence_nextval(seq), gen_no+1, stop_at 
             from seq_gen 
            where gen_no < stop_at        
        )
    select seq
      from seq_gen;
    
    Login or Signup to reply.
  3. Firstly, I did this (all of the code below is available here).

    SELECT
      SUBSTRING('ABCDE123', s.i, 1) AS c1,
      SUBSTRING('ABCDE123', t.j, 1) AS c2,
      SUBSTRING('ABCDE123', u.k, 1) AS c3
    FROM
      GENERATE_SERIES(1, 8) AS s(i),
      GENERATE_SERIES(1, 8) AS t(j),
      GENERATE_SERIES(1, 8) AS u(k)
    ORDER BY c1, c2, c3
    LIMIT 10;
    

    Result:

    c1  c2  c3
    1   1   1
    1   1   2
    1   1   3
    1   1   A
    1   1   B
    ...
    ... snipped for brevity
    ...
    

    But, you want it sorted AAA, AAB… but 1 is before A in the ASCII table, so we have to do this:

    WITH cte1 AS
    (
      SELECT
    
        SUBSTRING('ABCDE123', s.i, 1) AS c1,
        SUBSTRING('ABCDE123', t.j, 1) AS c2,
        SUBSTRING('ABCDE123', u.k, 1) AS c3
      
      FROM
        GENERATE_SERIES(1, 8) AS s(i),
        GENERATE_SERIES(1, 8) AS t(j),
        GENERATE_SERIES(1, 8) AS u(k)
    )
    SELECT 
      c1 || c2 || c3 AS sequence
    FROM
      cte1
    ORDER BY 
    
      CASE
        WHEN ASCII(c1) < 58 THEN ASCII(c1) + 45 --  45 is arbitrary, as long as it gets
        ELSE ASCII(c1)                          --  (ASCII(c1) + 45 > 90). Check an ASCII table!
      END,
      
      CASE
        WHEN ASCII(c2) < 58 THEN ASCII(c2) + 45
        ELSE ASCII(c2)
      END,
      
      CASE
        WHEN ASCII(c3) < 58 THEN ASCII(c3) + 45
        ELSE ASCII(c3)
      END
    LIMIT 10;
    

    Result:

    sequence
    AAA
    AAB
    AAC
    AAD
    AAE
    AA1
    ...
    ... snipped for brevity
    ...
    

    So, now we have the correct sort order.

    We can put all of this in a function as follows:

    CREATE OR REPLACE FUNCTION seq(str TEXT)
    RETURNS SETOF TEXT
    LANGUAGE SQL IMMUTABLE
    AS
    $$
      WITH cte1 AS
      (
        SELECT
    
          SUBSTRING($1, s.i, 1) AS c1,
          SUBSTRING($1, t.j, 1) AS c2,
          SUBSTRING($1, u.k, 1) AS c3
      
        FROM
          GENERATE_SERIES(1, LENGTH($1)) AS s(i),
          GENERATE_SERIES(1, LENGTH($1)) AS t(j),
          GENERATE_SERIES(1, LENGTH($1)) AS u(k)  
      )
    
      SELECT 
        c1 || c2 || c3 AS sequence
      FROM
      cte1
      ORDER BY 
    
        CASE
          WHEN ASCII(c1) < 58 THEN ASCII(c1) + 45  -- <<= 45 is arbitrary, as long as it gets
          ELSE ASCII(c1)                           --     it above 90. Check an ASCII table!
        END,
      
        CASE
          WHEN ASCII(c2) < 58 THEN ASCII(c2) + 45
          ELSE ASCII(c2)
        END,
      
        CASE
          WHEN ASCII(c3) < 58 THEN ASCII(c3) + 45
          ELSE ASCII(c3)
        END
    $$;
    

    and the run:

    SELECT
      seq('ABC1')
      LIMIT 10;
    

    Result:

    seq
    AAA
    AAB
    AAC
    AA1
    ABA
    ABB
    ...
    ... snipped for brevity
    ...
    

    With thanks to @Belayer’s code for the inspiration on the function!

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search