Alpha numeric sequence in SQL Server

Question

I need to create a 3-character alphanumeric sequence in SQL Server 2008 as follows:

001, 002, ..., 999, A01, A02, ..., A99, B01, B02, ..., Z99 

The next element in the sequence will be generated from the stored procedure and stored in the column of the NCHAR(3) table.

Answer 1

To get the following sequence, you can add Id as

 WITH seq AS ( SELECT ROW_NUMBER() OVER (ORDER BY x.alpha + y.number + z.number) AS Id, CONVERT(nchar(3), x.alpha + y.number + z.number) AS Result FROM ( VALUES ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z') ) x(alpha), ( VALUES ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9') ) y(number), ( VALUES ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9') ) z(number) WHERE NOT (NOT x.alpha BETWEEN '1' AND '9' AND y.number = '0' AND z.number = '0') ) -- Uncomment to see all results --SELECT * FROM seq SELECT Result FROM seq WHERE Id = (SELECT Id + 1 FROM seq WHERE Result = 'Z01') 

Result

 Z02 

Full score = 3573 = 999 + (26 * 99)

Answer 2

Another approach to your proposed stored procedure would be to use CROSS JOINs, proposed by user @ user2864740 and having the @pLastUsed parameter. Then SP returns the following sequence. NB This will work for the whole version of SQL Server.

 CREATE PROCEDURE NextID @pLastUsed CHAR(3) AS /* Usage EXEC NextID 'J64' */ BEGIN SET NOCOUNT ON; DECLARE @T1 TABLE (Col CHAR(1)) DECLARE @T2 TABLE (Col CHAR(1)) DECLARE @T3 TABLE (Col CHAR(1)) INSERT @T1 ( Col ) SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9' UNION ALL SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL SELECT 'D' UNION ALL SELECT 'E' UNION ALL SELECT 'F' UNION ALL SELECT 'G' UNION ALL SELECT 'H' UNION ALL SELECT 'I' UNION ALL SELECT 'J' UNION ALL SELECT 'K' UNION ALL SELECT 'L' UNION ALL SELECT 'M' UNION ALL SELECT 'N' UNION ALL SELECT 'O' UNION ALL SELECT 'P' UNION ALL SELECT 'Q' UNION ALL SELECT 'R' UNION ALL SELECT 'S' UNION ALL SELECT 'T' UNION ALL SELECT 'U' UNION ALL SELECT 'V' UNION ALL SELECT 'W' UNION ALL SELECT 'X' UNION ALL SELECT 'Y' UNION ALL SELECT 'Z' INSERT @T2 ( Col ) SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9' INSERT @T3 ( Col ) SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9' SELECT TOP 1 t1.Col + t2.Col + t3.Col FROM @T1 t1 CROSS JOIN @T2 AS t2 CROSS JOIN @T3 AS t3 WHERE t1.Col + t2.Col + t3.Col > @pLastUsed ORDER BY t1.Col + t2.Col + t3.Col END GO 

Answer 3

Sharing one example to generate an alphanumeric sequence in SQL Server.

For more information, you can visit this article.

Create sample table:

 CREATE TABLE dbo.tbl_TestVarcharSequence ( VarcharID VARCHAR(10) ,Name VARCHAR(255) ,CONSTRAINT pk_tbl_TestVarcharSequence_VarcharID PRIMARY KEY(VarcharID) ) GO 

Create a sample sequence object:

 CREATE SEQUENCE dbo.seq_TestVarcharSequenceNumber AS INT START WITH 1 INCREMENT BY 1; GO 

Create a sequence constraint using the FORMAT function:

 ALTER TABLE dbo.tbl_TestVarcharSequence ADD CONSTRAINT seq_tbl_TestVarcharSequence_VarcharID DEFAULT FORMAT((NEXT VALUE FOR dbo.seq_TestVarcharSequenceNumber),'ABC00#') FOR VarcharID; GO 

Insert a few sample entries:

 INSERT INTO dbo.tbl_TestVarcharSequence (Name) VALUES ('Anvesh'),('Alex'),('Roy'),('Bony') GO 

Result:

 SELECT * FROM dbo.tbl_TestVarcharSequence ABC001 Anvesh ABC002 Alex ABC003 Roy ABC004 Bony