Question 115053 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL generate and increment alphanumeric series

as the question says, I need postgresql procedure which will generate Some ids like:

AX0
AX1
AX2
.
.
AX9
AXA
AXB
.
.
AXZ
AY0
AY1
.
.
AY9
AYA
AYB
.
.
AZZ
B00
B01
B02
.
.
etc

And the proceedure can also generate next id when given previous, like send AST and return ASU, or 23X to return 23Y……etc.

Example: SELECT db.stored_proceedure(‘AST’);

As a novice, for now all I have is:

with 
letters as
(select chr(i) as letter from generate_series(65,90) i),
digits as
(select lpad(i::text,1,'0') as digit from generate_series(0,9) i)
select l1.letter || l2.letter || l3.letter || l4.letter || d.digit
from       letters l1
cross join letters l2
cross join letters l3
cross join letters l4
cross join digits d
limit 2000

Which only generate something like:

AAAA8
AAAA9
AAAB0
AAAB1
AAAB2
AAAB3
.....
AAAB8
AAAB9
AAAC0
AAAC1
AAAC2

Gurus in the house, please help. Thanks

Tags:

3

Answers


  1. 0

    For a 3-char value that includes 0-9 and then A-Z you could produce the next value with the query:

    with p (q) as (values ('000'), ('999'), ('99Z'), ('9ZZ'), ('AST'), ('23X'))
select q, m
from (
  select q, substr(q, 1, 1) as a, substr(q, 2, 1) as b, substr(q, 3, 1) as c from p
) u,
lateral (
  select (ascii(a) - case when a <= '9' then 48 else 55 end) * 36 * 36 +
         (ascii(b) - case when b <= '9' then 48 else 55 end) * 36 +
          ascii(c) - case when c <= '9' then 48 else 55 end + 1 as n
) v,
lateral (select n / 36 / 36 as d0, mod(n, 36 * 36) as r0) x,
lateral (select r0 / 36 as d1, mod(r0, 36) as d2) y,
lateral (
  select chr(case when d0 < 10 then 48 else 55 end + d0) ||
         chr(case when d1 < 10 then 48 else 55 end + d1) ||
         chr(case when d2 < 10 then 48 else 55 end + d2) as m
) z

    Result:

     q    m   
 ---- --- 
 000  001 
 999  99A 
 99Z  9A0 
 9ZZ  A00 
 AST  ASU 
 23X  23Y

    See db<>fiddle.

    Change values in line 1 to see more examples.

    Login or Signup to reply.
  2. 0

    I did a base36 conversion with PostgreSQL a while back. I thought I had posted it here but cannot find it in my history:

    with invars as (
  select num
    from generate_series(0, 1000, 1) as gs(num)
), powers as (
  select n + 1 as n,
         (36^n)::numeric as b36
    from generate_series(0,10) as gs(n)
), symbols as (
  select i as dval, chr(i + 48) as dchar
    from generate_series(0,9) as gs(i)
  union all
  select i + 10 as dval, chr(i + 65) as dchar
    from generate_series(0,25) as gs(i)
), indigits as (
  select num, n, floor((num % lead(b36) over (partition by num order by n))/b36) as b36d
    from invars
         cross join powers
)
select i.num, trim(leading '0' from string_agg(s.dchar, '' order by n desc)) as value
  from indigits i
       join symbols s on s.dval = i.b36d
 group by i.num;

    db<>fiddle here

    Login or Signup to reply.
  3. 0

    You could do it like this, it would create a string that has all the characters in my case with 4 letters and a number. Then you select as many as you need.

    The CTE numberletters defines how many letters there ca be.

    this will need some space run with 8 letters.

    with 
letters as
(select chr(i) as letter from generate_series(65,90) i),
lettersnum as
(select chr(i) as letter from generate_series(48,57) i
UNION 
select chr(i) as letter from generate_series(65,90) i),
numberletters as
(select i as num from generate_series(2,4) i)
SELECT DISTINCT RIGHT(l1.letter || l3.letter || l4.letter 
  || l2.letter,num)  col , LENGTH(RIGHT(l1.letter || l3.letter || l4.letter 
  || l2.letter,num)) len
FROM letters l1 CROSS JOIN letters l3 CROSS JOIN letters l4 
CROSS JOIN  lettersnum l2 CROSS JOIN numberletters
ORDER By len, col
LIMIT 30

    col | len
:-- | --:
A0  |   2
A1  |   2
A2  |   2
A3  |   2
A4  |   2
A5  |   2
A6  |   2
A7  |   2
A8  |   2
A9  |   2
AA  |   2
AB  |   2
AC  |   2
AD  |   2
AE  |   2
AF  |   2
AG  |   2
AH  |   2
AI  |   2
AJ  |   2
AK  |   2
AL  |   2
AM  |   2
AN  |   2
AO  |   2
AP  |   2
AQ  |   2
AR  |   2
AS  |   2
AT  |   2

    db<>fiddle here

    For incresing you need a lot of con version

    select substring('PG1' from '[0-9]+$') as num,
       encode(
          decode(
              (
                  (encode
                      (substring
                          ('PG1' from '^[A-Za-z]+')::byteA, 'hex'
                      )::int
                  ) + 1
              )::TEXT, 'hex'
      ), 'escape'
      ) as text_

    num | text_
:-- | :----
1   | PH

    db<>fiddle here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search