SQL Server获取下一个编码字符实现

    周末看到SQL Server 大V潇湘隐者的获取下一个编码字符串问题，本来作为以上博文的回复，也许回复内容长度超过其允许限制，无法提交。鉴于此，特记录SQL Server实现过程，方便自己回顾和查阅。

    我简单总结编码字符的规则如下：
1、5位长度，只能包含0-9数字字符和A-Z（大写）字母字符，且第一位从A开始，最小编码字符为A0000，最大编码字符为ZZZZZ。
2、编码字符是递进增加的，例如：首个编码是A0000，下一个是A0001，直到A9999，其下一个是B0000，直到B9999，其下一个是C0000，……，如果编码是ZB999，下一个是ZC000，……，直到ZZZZZ。
具体的规则请参看获取下一个编码字符串问题。
 
    从规则入手分析规则2，编码字符是递进增加，间隔为1，也就是一个连续的整数序列，开始整数值和结束整数值固定的。如果编码字符和整数数值之间实现某种相互转换，那么这个问题也就解决啦。
 
    从规则1来看，需要定义硬编码实现0-9数字字符和A-Z（大写）字母字符与相应10进制整数值的对应；还需要实现将一个编码字符转化为整数数值；当然也需要实现将一个整数数值转化为一个编码字符；目前仅是满足5位长度，如果以后扩充到6位，更多位数的需求产生了呢，这个需要设置编码字符的统一固定长度。以上四个方面我分别定义其对应的函数来实现：硬编码字符映射表值函数、转换编码字符为整数数值的标量函数、转换整数数值为编码字符的标量函数和设置编码字符固定长度的标量函数。
 
 
 硬编码字符映射表值函数
 
该函数的T-SQL代码如下：

 1 IF OBJECT_ID(N'dbo.ufn_GetCodeChars', 'TF') IS NOT NULL 2 BEGIN 3   DROP FUNCTION dbo.ufn_GetCodeChars; 4 END 5 GO 6  7 --================================== 8 -- 功能: 获取编码字符表函数 9 -- 说明: 编码字符只包含0-9和A-Z这两类字符10 --    将以上字符映射到对应十进制数值。11 -- 作者: XXX12 -- 创建: yyyy-MM-dd13 -- 修改: yyyy-MM-dd XXX 修改内容描述14 -- 调用: SELECT CodeChar, CodeValue FROM dbo.ufn_GetCodeChars();15 --==================================16 CREATE FUNCTION dbo.ufn_GetCodeChars17 (18 ) RETURNS @tblCodeChars TABLE (19   CodeChar CHAR(1) NOT NULL,20   CodeValue TINYINT NOT NULL21 )22   --$Encode$--23 AS24 BEGIN25   DECLARE26     @intStartIndexID AS TINYINT,27     @intEndIndexID AS TINYINT;28 29   SELECT30     @intStartIndexID = 0,31     @intEndIndexID = 0;32 33   -- 初始化0-9数字字符34   SELECT35     @intStartIndexID = ASCII('0'),36     @intEndIndexID = ASCII('9');37   WHILE @intStartIndexID <= @intEndIndexID38   BEGIN39     INSERT INTO @tblCodeChars (CodeChar, CodeValue)40     VALUES (CHAR(@intStartIndexID), 0);41 42     SET @intStartIndexID = @intStartIndexID + 1;     43   END44 45   -- 初始化A-Z字母字符46   SELECT47     @intStartIndexID = ASCII('A'),48     @intEndIndexID = ASCII('Z');49   WHILE @intStartIndexID <= @intEndIndexID50   BEGIN51     INSERT INTO @tblCodeChars (CodeChar, CodeValue)52     VALUES (CHAR(@intStartIndexID), 0);53 54     SET @intStartIndexID = @intStartIndexID + 1;     55   END56 57   -- 修改每个编码字符对应的编码值58   ;WITH tCodeData AS (59     SELECT CodeChar, ROW_NUMBER() OVER (ORDER BY CodeChar ASC) AS RowNum60     FROM @tblCodeChars61   )62 63   UPDATE T264   SET T2.CodeValue = T.RowNum - 165   FROM tCodeData AS T66     INNER JOIN @tblCodeChars AS T267       ON T.CodeChar = T2.CodeChar;68 69   RETURN;70 END71 GO

 

设置编码字符固定长度的标量函数
 
该函数的T-SQL代码如下：

 

 1 IF OBJECT_ID(N'dbo.ufn_GetCodeCharFixLength', 'FN') IS NOT NULL 2 BEGIN 3   DROP FUNCTION dbo.ufn_GetCodeCharFixLength; 4 END 5 GO 6  7 --================================== 8 -- 功能: 获取编码字符组合的固定长度 9 -- 说明: 如果转化为int数据类，只能是8位整数，且字符串编码的固定长度只能是8，仅支持5到8位编码字符的组合10 -- 作者: XXX11 -- 创建: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改内容描述13 -- 调用: SELECT dbo.ufn_GetCodeCharFixLength();14 --==================================15 CREATE FUNCTION ufn_GetCodeCharFixLength16 (17 ) RETURNS TINYINT18   --$Encode$--19 AS20 BEGIN21   RETURN CAST(5 AS TINYINT);22 END23 GO

 

转换编码字符为整数数值的标量函数

 
该函数的T-SQL代码如下：

 1 IF OBJECT_ID(N'dbo.ufn_GetCodeIntegerValue', 'FN') IS NOT NULL 2 BEGIN 3   DROP FUNCTION dbo.ufn_GetCodeIntegerValue; 4 END 5 GO 6  7 --================================== 8 -- 功能: 通过编码字符获取其对应的整数数值 9 -- 说明: 具体实现阐述10 -- 作者: XXX11 -- 创建: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改内容描述13 -- 调用: SELECT dbo.ufn_GetCodeIntegerValue('A0000')14 --==================================15 CREATE FUNCTION dbo.ufn_GetCodeIntegerValue16 (17   @chCodeChar CHAR(5)18 19 ) RETURNS INT20   --$Encode$--21 AS22 BEGIN23   SET @chCodeChar = ISNULL(@chCodeChar, '');  24   SET @chCodeChar = UPPER(@chCodeChar);25   DECLARE @intCodeIntegerValue AS INT;26   SET @intCodeIntegerValue = 0;27 28   DECLARE @tintFixLength AS TINYINT;29   SET @tintFixLength =dbo.ufn_GetCodeCharFixLength();30 31   DECLARE @tintLength AS TINYINT;32   SET @tintLength = LEN(@chCodeChar);33 34   IF @tintLength <= (@tintFixLength - 1) OR @tintLength >= (@tintFixLength + 1)35   BEGIN36     RETURN @intCodeIntegerValue;37   END38 39   DECLARE @tblCodeChars TABLE(40     CodeChar CHAR(1) NOT NULL,41     CodeValue TINYINT NOT NULL42   );43 44   INSERT INTO @tblCodeChars (CodeChar, CodeValue)45   SELECT CodeChar, CodeValue46   FROM dbo.ufn_GetCodeChars();47 48   WHILE @tintLength >= 149   BEGIN  50     SELECT @intCodeIntegerValue = @intCodeIntegerValue + CodeValue * POWER(10, @tintFixLength - @tintLength)51     FROM @tblCodeChars52     WHERE CodeChar = SUBSTRING(@chCodeChar, @tintLength, 1);53 54     SET @tintLength = @tintLength - 1;    55   END56 57   RETURN @intCodeIntegerValue;58 END59 GO

 

转换为整数数值为编码字符的标量函数
该函数的T-SQL代码如下：

 1 IF OBJECT_ID(N'dbo.ufn_GetCodeChar', 'FN') IS NOT NULL 2 BEGIN 3   DROP FUNCTION dbo.ufn_GetCodeChar; 4 END 5 GO 6  7 --================================== 8 -- 功能: 通过编码整数值获取对应的编码字符 9 -- 说明: 具体实现阐述 10 -- 作者: XXX11 -- 创建: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改内容描述13 --==================================14 CREATE FUNCTION dbo.ufn_GetCodeChar15 (16   @intCodeIntegerValue INT17 ) RETURNS CHAR(5)18   --$Encode$--19 AS20 BEGIN21   SET @intCodeIntegerValue = ISNULL(@intCodeIntegerValue, 0);22   DECLARE @chCodeChar AS VARCHAR(9);23   SET @chCodeChar = '';24 25   DECLARE @tintFixLength AS TINYINT;26   SET @tintFixLength =dbo.ufn_GetCodeCharFixLength();27 28   IF @intCodeIntegerValue NOT BETWEEN dbo.ufn_GetCodeIntegerValue('A' + REPLICATE('0', @tintFixLength - 1)) AND dbo.ufn_GetCodeIntegerValue(REPLICATE('Z', @tintFixLength))29   BEGIN30     RETURN @chCodeChar;31   END32 33   DECLARE @tblCodeChars TABLE(34     CodeChar CHAR(1) NOT NULL,35     CodeValue TINYINT NOT NULL36   );37 38   INSERT INTO @tblCodeChars (CodeChar, CodeValue)39   SELECT CodeChar , CodeValue40   FROM dbo.ufn_GetCodeChars();41 42   DECLARE @tintPerCodeValue TINYINT;43   SET @tintPerCodeValue = 0;44 45   WHILE @tintFixLength >= 146   BEGIN    47     SET @tintPerCodeValue = @intCodeIntegerValue / POWER(10, @tintFixLength - 1);48 49     SELECT TOP 1 @chCodeChar = @chCodeChar + CodeChar, @tintPerCodeValue = CodeValue 50     FROM @tblCodeChars51     WHERE CodeValue <= @tintPerCodeValue52     ORDER BY CodeValue DESC;53 54     SET @intCodeIntegerValue = @intCodeIntegerValue - @tintPerCodeValue * POWER(10, @tintFixLength - 1);55 56     SET @tintFixLength = @tintFixLength - 1;57   END58 59   RETURN @chCodeChar;60 END61 GO62 

 

测试实现效果

 
测试的T-SQL代码如下：

 1 DECLARE @chCodeChar AS CHAR(8); 2 SET @chCodeChar = 'A0000'; 3 DECLARE @intValue AS INT; 4 SET @intValue = dbo.ufn_GetCodeIntegerValue(@chCodeChar); 5  6 SELECT @chCodeChar AS CurrentCodeChar, @intValue AS CurrentCodeIntegerValue, dbo.ufn_GetCodeChar(@intValue + 1) AS NextCodeChar; 7 GO 8  9 DECLARE @chCodeChar AS CHAR(8);10 SET @chCodeChar = 'ZZZZY';11 DECLARE @intValue AS INT;12 SET @intValue = dbo.ufn_GetCodeIntegerValue(@chCodeChar);13 14 SELECT @chCodeChar AS CurrentCodeChar, @intValue AS CurrentCodeIntegerValue, dbo.ufn_GetCodeChar(@intValue + 1) AS NextCodeChar;15 GO

 

执行后的查询结果如下：


 
实现方案的限制
 
    该实现方案只能实现编码字符长度最多为8位的编码字符与整数数值的相互转换。如果要要实现编码字符固定长度更长的（比如编码字符固定长度为6位、7位或8位）功能，必须要修改三个函数，具体的修改处如下图：



以上图红色矩形框标注的地方，务必要一直才可以的。如果全部更改为6，那就满足编码字符固定长度为6位的实现；也可以修改为7或8，最多只能修改为8。
 


 
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。
 
 

原标题：SQL Server获取下一个编码字符实现

关键词：sql