Autoincrement value

Question

I'm working on a local project and I would like to setup a column in a SQL Server table that I called serial. This value should have the next format/validation [A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]. Four characters that will have numbers or capital letters.

For example, the first record would be AAA0, and then start incrementing from right to left, but the interesting part is that once it reaches AAA9, I want to continue but with letters, so once 9 is reached, continue with A: AAAA. My last combination would be AAAZ, and then continue with the second position and so on, until I could complete ZZZZ.

Serial
------
AAA0
…
AAAZ
AAB0
…
AABZ
AAC0
…
AACZ
…
ZZZZ

I've been able to do something similar, but for example only incrementing numbers 0000 until 9999 or with just letters AAAA to ZZZZ (see below dummy data).

Please let me know if you have any comments or if you have seen this scenario before. I will appreciate any help.

I've reviewed some data on the internet and did this small test, but once again, this is changing letters.

CREATE TABLE #MyTable
(
    MyHeadID INT IDENTITY(0,1) NOT NULL,
    Consecutive AS 
            CHAR(MyHeadID/17576%26+65) +        --26^3
            CHAR(MyHeadID/676%26+65)  +         --26^2
            CHAR(MyHeadID/26%26+65)  +          --26^1
            CHAR(MyHeadID%26+65)                --26^0
                PERSISTED  NOT NULL,
    UniqueID VARCHAR(36) NOT NULL,
    CreatedDate datetime DEFAULT GETDATE()
)

 INSERT INTO #MyTable (UniqueID) 
    (SELECT NEWID())

 SELECT Consecutive FROM #MyTable

Let's not talk about the UniqueID and CreatedDate columns, those are just for testing purposes. I created an identity column and then the code for the autoincrement on letters.

I also found this reply here but it's not my case since in that reply they are dividing 4 letters and 4 numbers with a substring it can be resolved. In my case, I need to intercalculate numbers and letters from right to left.

Answer 1

Here's a possible solution, counting in base 36 AAA0 starts at decimial 479879.

Ideally you'd have an identity incrementing number on your table starting at 479879. Simulating that with a numbers table generated using a CTE, the following gives base36 counting:

with numbers as (
    select 479879 + Row_Number() over (order by a.object_id)n 
    from sys.all_objects a cross join sys.all_objects b
)
select top 10000
    Concat(Char(((n/36/36/36) % 36) + case when (n/36/36/36) % 36 between 0 and 9 then 48 else 55 end),
    Char(((n/36/36) % 36) + case when (n/36/36) % 36 between 0 and 9 then 48 else 55 end),
    Char(((n/36) % 36) + case when (n/36) % 36 between 0 and 9 then 48 else 55 end),
    Char((n % 36) + case when n % 36 between 0 and 9 then 48 else 55 end)) Base36
from numbers 
order by n

See Fiddle