SQL Server 中的字母数字序列 [英] Alpha-numeric sequence in SQL Server

问题描述

我需要在 SQL Server 2008 中生成一个 3 个字符的字母数字序列，如下所示:

I need to generate a 3 character alphanumeric sequence, in SQL Server 2008, as follows:

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

序列中的下一项将从存储过程生成并存储在 NCHAR(3) 表列中.

The next item in the sequence will get generated from a stored procedure and stored in a NCHAR(3) table column.

推荐答案

要获得下一个序列，您可以添加一个 Id like

To get the next sequence you can add a Id like

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')

结果

Z02

完整计数 = 3573 = 999 + (26 * 99)

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

这篇关于SQL Server 中的字母数字序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！