PostgreSQL generate and increment alphanumeric series-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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