[ SQL to generate next sequence in an alphanumeric id ]

I gained some help from this question, but still need some further assistance.

I need to be able to generate the next available 2-digit alphanumeric code. I cannot change the table definition, before you ask. I am working in T-SQL.

So, for example, let's say I have the sequence

00, 01, 02,..., 09, 0A, 0B, 0C,..., 0Y, 0Z, 10, 11,...1Y, 1Z, 20, 21,..., 9Y, 9Z, I would like for the next id to be A0,

then A1, A2, ..., A9, AA, AB, AC, ..., AZ, I would like for the next id to be B0, then B1, etc.

So, in short, I would like to go from 00 all the way to ZZ and each time look for the MAX in that field and assign a new code 1 greater than the max. I would understand that A > 9, and the first column greater than the second, so A0 > 99 and AA > A9.

I wish I could just assign a numeric id to all of this, but the table definition is more critical at this point and so I'm not allowed to change it, so I am trying to maximize the available ids I'll have in such a limited space.

Thank you for your help.

Answer 1

---

Have a look at this. This is a really nasty problem for ID's. You've effectively limited yourself a low number of permutations of the key with 2 characters. Also you have a problem that you'll need to deal with if ZZ is used and this algorithm runs again. I have expanded these into as logical steps as possible for demonstration, but feel free to condense as needed.

DECLARE @ExistingTable TABLE (ID CHAR(2))
INSERT INTO @ExistingTable (ID) VALUES ('5A'),('5B')

DECLARE @NewID CHAR(2)

;WITH
Ranks AS (
    SELECT '0' AS [Character] UNION SELECT '1' AS [Character] UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION
    SELECT '7' UNION SELECT '8' UNION SELECT '9' UNION SELECT 'A' UNION SELECT 'B'UNION
    SELECT 'C' UNION SELECT 'D' UNION SELECT 'E' UNION SELECT 'F' UNION SELECT 'G' UNION SELECT 'H' UNION
    SELECT 'I' UNION SELECT 'J' UNION SELECT 'K' UNION SELECT 'L' UNION SELECT 'M' UNION SELECT 'N' UNION
    SELECT 'O' UNION SELECT 'P' UNION SELECT 'Q' UNION SELECT 'R' UNION SELECT 'S' UNION SELECT 'T' UNION
    SELECT 'U' UNION SELECT 'V' UNION SELECT 'W' UNION SELECT 'X' UNION SELECT 'Y' UNION SELECT 'Z'
), Permutations AS (
    SELECT  SecondChar.[Character] + FirstChar.[Character] AS PermuteID
    FROM    Ranks AS FirstChar
            CROSS JOIN Ranks AS SecondChar
), PermutationsKeyed AS (
    SELECT  ROW_NUMBER() OVER (ORDER BY PermuteID ASC) AS PrimaryKeyHolder,
            PermuteID
    FROM    Permutations
), MaxPK AS (
    SELECT  MAX(Perm.PrimaryKeyHolder) + 1 AS MaxPK
    FROM    @ExistingTable AS E
            INNER JOIN PermutationsKeyed AS Perm ON (E.ID = Perm.PermuteID)
)
SELECT  @NewID = Perm.PermuteID
FROM    PermutationsKeyed AS Perm
        INNER JOIN MaxPK AS M ON (Perm.PrimaryKeyHolder = M.MaxPK)


SELECT @NewID

Answer 2

---

I'm not sure how you wanted to go about returning the next value but I think this a simple and efficient ways to get all your values. Let me know if you need anything else.

DECLARE @values TABLE (val CHAR(1));
DECLARE @int        INT = 48,
        @letters    INT = 65;

IF OBJECT_ID('dbo.tbl_keys') IS NOT NULL
    DROP TABLE dbo.tbl_keys;

--This will hold the values so you can always reference them
CREATE TABLE dbo.tbl_Keys
(
    --Primary key will create a clustered index on rank_id by default
    rank_id INT PRIMARY KEY,
    ID_Code CHAR(2)
);
--Another index on ID_Code
CREATE NONCLUSTERED INDEX idx_ID_Code ON tbl_keys(ID_Code);

--This is how I get all your individual values
WHILE (SELECT COUNT(*) FROM @values) < 36
BEGIN
    IF(@int <= 57)
        INSERT INTO @values VALUES(CHAR(@int));

    INSERT INTO @values
    VALUES (CHAR(@letters))

    SET @int = @int + 1;
    SET @letters = @letters + 1;
END

--Insert all possible combinations and rank them
INSERT INTO tbl_Keys
    --ASCII is your best friend. It returns the ASCII code(numeric value) for characters
    SELECT  ROW_NUMBER() OVER (ORDER BY ASCII(A.val),ASCII(B.val)) AS rank_id,
            A.val + B.val ID
    FROM @values A
    CROSS JOIN @values B;

I provide two different ways of getting the next ID_code(Read comments):

--Here's some dummy data
WITH CTE_DummyTable
AS
(
    SELECT '00' ID_Code
    UNION ALL
    SELECT '01'
    UNION ALL
    SELECT '02'
)


----Here's how to get the next value with the assumption there are no gaps in your data
--SELECT MIN(ID_Code) next_id_code
--FROM tbl_Keys
--WHERE ID_code > (SELECT MAX(id_code) FROM CTE_DummyTable)

--This one doesn't assume the gaps and returns the lowest available ID_code
SELECT MIN(ID_Code) next_id_code
FROM tbl_Keys
WHERE ID_code NOT IN (SELECT DISTINCT id_code FROM CTE_DummyTable)

Note: If you were ever to want to convert your alphanumeric values really easily for whatever reason without changing the rank try this.

SELECT  rank_id,
    ID_code,
    CAST(CONCAT(ASCII(LEFT(id_code,1)),ASCII(RIGHT(id_code,1))) AS INT) AS numeric_id_code
FROM tbl_Keys