The sequence asked for is a combination sequence of alpha (bijective) hexavigesimal(base 26) and numerical decimal (base 10). The pattern proposed has eight significant digits with the maximum value of zz-z99-z99 = (26)(26)(26)(10)(10)(26)(10)(10) = 4.56976 x 10^9 = 4569760000.
😎
Constructing the sequence is relatively straight forward, first consider this table:
Max Value a a a 0 0 a 0 0 Significance
| (26)(26)(26)(10)(10)(26)(10)(10)
| | | | | | | | |
| | | | | | | | '--> 1
| | | | | | | '------> 10
| | | | | | '----------> 100
| | | | | '--------------> 2600
| | | | '------------------> 26000
| | | '----------------------> 260000
| | '--------------------------> 6760000
| '------------------------------> 175760000
'----------------------------------------> 4569760000
Using this matrix, we can now set the iteration for each digit in the sequence with a combination of division and modulo operation, this code samples use an inline Tally CTE to construct exactly one fully populated set, the first sample is for SQL Server 2012 and later, the latter sample is for SQL Server 2005 - 2008. Note that the code is left very verbose to make it self explanatory:
SQL Server 2012 - 1014
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE BIGINT = 4569760000;
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9,T T10,T T11)
SELECT
NM.N
,NM.N % 4569760000
,CONCAT(
CHAR(97 + FLOOR( (NM.N % 4569760000) / 175760000))
, CHAR(97 + FLOOR( ((NM.N % 4569760000) % 175760000) / 6760000))
, CHAR(45)
, CHAR(97 + FLOOR( (((NM.N % 4569760000) % 175760000) % 6760000) / 260000))
, FLOOR( ((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) / 26000)
, FLOOR( (((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) % 26000) / 2600)
, CHAR(45)
, CHAR(97 + FLOOR( ((((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) % 26000) % 2600) / 100))
, FLOOR((((((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) % 26000) % 2600) % 100) / 10)
, FLOOR((((((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) % 26000) % 2600) % 100) % 10)
)
FROM NUMS NM;
SQL Server 2005 - 2008
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE BIGINT = 4569760000;
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9,T T10,T T11)
SELECT
NM.N
,NM.N % 4569760000
, CHAR(97 + FLOOR( (NM.N % 4569760000) / 175760000) )
+ CHAR(97 + FLOOR( ((NM.N % 4569760000) % 175760000) / 6760000) )
+ CHAR(45)
+ CHAR(97 + FLOOR( (((NM.N % 4569760000) % 175760000) % 6760000) / 260000) )
+ CAST( FLOOR( ((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) / 26000) AS CHAR(1))
+ CAST( FLOOR( (((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) % 26000) / 2600) AS CHAR(1))
+ CHAR(45)
+ CHAR(97 + FLOOR( ((((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) % 26000) % 2600) / 100) )
+ CAST( FLOOR((((((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) % 26000) % 2600) % 100) / 10) AS CHAR(1))
+ CAST( FLOOR((((((((NM.N % 4569760000) % 175760000) % 6760000) % 260000) % 26000) % 2600) % 100) % 10) AS CHAR(1))
FROM NUMS NM;
The same matrix as used for constructing the sequence can then also be used to translate it back to it's numerical equivalent:
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SEQ_STR CHAR(10) = 'ac-k97-g27';
SELECT
CONVERT(BIGINT,ASCII(SUBSTRING(@SEQ_STR,1, 1)) - 97 ,0) * 175760000
+ CONVERT(BIGINT,ASCII(SUBSTRING(@SEQ_STR,2, 1)) - 97 ,0) * 6760000
+ CONVERT(BIGINT,ASCII(SUBSTRING(@SEQ_STR,4, 1)) - 97 ,0) * 260000
+ CONVERT(BIGINT, SUBSTRING(@SEQ_STR,5, 1) ,0) * 26000
+ CONVERT(BIGINT, SUBSTRING(@SEQ_STR,6, 1) ,0) * 2600
+ CONVERT(BIGINT,ASCII(SUBSTRING(@SEQ_STR,8, 1)) - 97 ,0) * 100
+ CONVERT(BIGINT, SUBSTRING(@SEQ_STR,9, 1) ,0) * 10
+ CONVERT(BIGINT, SUBSTRING(@SEQ_STR,10,1) ,0);