Happy New Year to masters and readers alike!

The OP wants a four byte counter:
- first byte from A through Z (26 possibilities)
- second byte from 0 through 9 then A through Z (36 possibilities)
- bytes 3-4 from 01 through 99 (99 possibilities)

With use of the CEIL and MOD functions, there should be a way of calculating every component from an integer:

with data(n)  as (
  select level from dual
  connect by level <= 26*36*99
)
select chr(ceil(n/99/36) + 64) ||
  chr(
    mod(ceil(n/99) - 1, 36) + 1
      + case when
    mod(ceil(n/99) - 1, 36) + 1
      < 11 then 47 else 54 end
  ) ||
  to_char(mod(n-1,99)+1, 'fm09') cd
from data;

Best regards,
Stew

Hi Chris,
Thanks for the quick response.

Apologies i dint make the question clear initially.

"How come you go from A999 -> AA01, not AA00? "

--> From A999 it should go to AA00 and increment accordingly.

"And how come only the second character seems to cycle through letters and numbers?"

--> its not just the second character, the full set of characters need to increment and cycle through.
Hope the below example makes it more clear.
The counter should start with
0000
0001
0002
.
.
9999
A000
A001
A002
.
.
A999
B000
B001
B002
.
.
Z000
Z001
.
.
Z999
AA00
AA01
.
.
AA99
AB00
AB01
.
.
AZ99
BA00
BA01
.
.
BZ99
CA01
CA02
.
CZ99
DA01
DA02
.
.
. so on till
ZZZZ

Kind of an excel sheet but with numbers involved.

Thanks for your response, will try to tweak that to match my requirements.

Hi,

With the latest clarification from the OP this reduces to the base-36 case, I think. Assuming leaving off some of the 00s again is an oversight. There are various implementations for that as PL/SQL functions or in SQL using CONNECT BY/LISTAGG. I tried my hand on a generic version for SQL :

WITH
Conf AS (SELECT 36 Base, 4 Len, 200 Cnt FROM DUAL),
Vals AS (SELECT ROWNUM - 1 DecVal FROM Conf CONNECT BY ROWNUM <= Cnt + 1),
Digs AS (SELECT ROWNUM - 1 Pos, Base FROM Conf CONNECT BY ROWNUM < Len),
NumDig AS (
  SELECT s.DecVal, d.Pos, TRUNC(MOD(s.DecVal / POWER(d.Base, d.Pos), d.Base)) Digit  
  FROM Vals s CROSS JOIN Digs d),
ANDig AS (
  SELECT DecVal, Pos, CASE WHEN Digit > 9 THEN CHR(Digit + 55) ELSE TO_CHAR(Digit) END Digit
  FROM NumDig)    
SELECT DecVal, LISTAGG(Digit, '') WITHIN GROUP (ORDER BY Pos DESC) Result
FROM   ANDig
GROUP BY DecVal
ORDER BY 1 DESC;

DECVAL RESULT
200 05K
199 05J
198 05I
197 05H
196 05G
195 05F
...

Assumes 0..9A..Z similar to hex. Unfortunately TO_CHAR has only native Hex support ('X') but not for generic bases.
Otherwise this would simply be

SELECT TO_CHAR(val, 'XXXX', 36) FROM DUAL; 

regards,

Assuming I understand the revised requirement, it is not just base-36 because counters like '000A' to '000Z' are skipped.

I see five ranges:
- 10000 counters from '0000' to '9999'
- 26*1000 counters from 'A000' to 'Z999'
- 26*26*100 from 'AA00' to 'ZZ99'
- 26*26*26*10 from 'AAA0' to 'ZZZ9'
- 26*26*26*26 from 'AAAA' to 'ZZZZ'.

with range_sizes as (
  select 10000 R0,
         26*1000 R1,
         26*26*100 R2,
         26*26*26*10 R3,
         26*26*26*26 R4
  from dual
)
, data(n) as (
  select level from range_sizes
  connect by level <= R0+R1+R2+R3+R4
)
, numbers as (
  select n n0,
    n - R0 n1,
    n - R0 - R1 n2,
    n - R0 - R1 - R2 n3,
    n - R0 - R1 - R2 - R3 n4
  from data, range_sizes
)
select case
  when n1 <= 0
    then to_char(n0-1,'fm0009')
  when n2 <= 0
    then chr(ceil(n1/1000) + 64)
      || to_char( mod(n1-1,1000), 'fm009')
  when n3 <= 0
    then chr(ceil(n2/26/100) + 64)
      || chr(mod((n2-1)/100,26) + 65)
      || to_char( mod(n2-1,power(10,2)), 'fm09')
  when n4 <= 0
    then chr(ceil(n3/26/26/10) + 64)
      || chr(mod((n3-1)/26/10,26) + 65)
      || chr(mod((n3-1)/10,26) + 65)
      || to_char( mod(n3-1,power(10,1)), 'fm9')
  else
    chr(ceil(n4/26/26/26) + 64)
      || chr(mod((n4-1)/26/26,26) + 65)
      || chr(mod((n4-1)/26,26) + 65)
      || chr(mod((n4-1),26) + 65)
  end counter
from numbers, range_sizes;

N0     COUNTER
1      0000
...
10000  9999
10001  A000
...
36000  Z999
36001  AA00
...
103600  ZZ99
103601  AAA0
...
279360  ZZZ9
279361  AAAA
...
736336  ZZZZ

Best regards,
Stew

Thanks Stew and Chris. :)

Hi,

Ok. So more of a sliding base26/10 hybrid. Tricky to do somewhat generic. Write only code ;)

WITH
Conf AS (SELECT 26 Base, 4 Len FROM DUAL),
Digs AS (SELECT ROWNUM - 1 Pos, Base FROM Conf CONNECT BY ROWNUM <= Len),
Lmts AS (SELECT Len - ROWNUM Pos, POWER(Base, ROWNUM - 1) * POWER(10, Len - ROWNUM + 1) Val FROM Conf CONNECT BY ROWNUM <= Len + 2),
SLts AS (SELECT Pos, SUM(Val) OVER (ORDER BY Pos DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Val FROM Lmts),
Rngs AS (SELECT Pos, Val - 1 UL, NVL(Lag(Val) OVER (ORDER BY Pos DESC), 0) LL FROM SLts),
Vals AS (SELECT LL + Offset DecVal FROM Rngs CROSS JOIN (SELECT ROWNUM - 2 OffSet FROM DUAL CONNECT BY ROWNUM <= 3)),
TypeDig AS (
  SELECT v.DecVal, d.Pos, CASE WHEN d.Pos > r.Pos THEN 'A' ELSE '0' END NT,
    TRUNC(MOD((v.DecVal - r.LL) / (POWER(d.Base, GREATEST(d.Pos - r.Pos - 1, 0)) * POWER(10, d.Pos - GREATEST(d.Pos - r.Pos - 1, 0))),
      CASE WHEN d.Pos > r.Pos THEN d.Base ELSE 10 END)) Digit   
  FROM   Vals v CROSS JOIN Digs d JOIN Rngs r ON (v.DecVal BETWEEN r.LL AND r.UL)),
ANDig AS (
  SELECT DecVal, Pos, CASE NT WHEN 'A' THEN CHR(Digit + 65) ELSE TO_CHAR(Digit) END Digit
  FROM   TypeDig)    
SELECT LISTAGG(Digit, '') WITHIN GROUP (ORDER BY Pos DESC) Result, DecVal
FROM   ANDig
GROUP BY DecVal
ORDER BY 2;

RESULT DECVAL
0000 0
0001 1
9999 9999
A000 10000
A001 10001
Z999 35999
AA00 36000
AA01 36001
ZZ99 103599
AAA0 103600
AAA1 103601
ZZZ9 279359
AAAA 279360
AAAB 279361
ZZZZ 736335
AAAA 736336
AAAA 736337

I made it start from 0 so off by one from the one by Stew.

regards,