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