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代码如下：

 IF OBJECT_ID(N'dbo.ufn_GetCodeChars', 'TF') IS NOT NULL
 BEGIN
     DROP FUNCTION dbo.ufn_GetCodeChars;
 END
 GO

 --==================================
 -- 功能: 获取编码字符表函数
 -- 说明: 编码字符只包含0-9和A-Z这两类字符
 --       将以上字符映射到对应十进制数值。
 -- 作者: XXX
 -- 创建: yyyy-MM-dd
 -- 修改: yyyy-MM-dd XXX 修改内容描述
 -- 调用: SELECT CodeChar, CodeValue FROM dbo.ufn_GetCodeChars();
 --==================================
 CREATE FUNCTION dbo.ufn_GetCodeChars
 (
 ) RETURNS @tblCodeChars TABLE (
     CodeChar CHAR() NOT NULL,
     CodeValue TINYINT NOT NULL
 )
     --$Encode$--
 AS
 BEGIN
     DECLARE
         @intStartIndexID AS TINYINT,
         @intEndIndexID AS TINYINT;

     SELECT
         @intStartIndexID = ,
         @intEndIndexID = ;

     -- 初始化0-9数字字符
     SELECT
         @intStartIndexID = ASCII(''),
         @intEndIndexID = ASCII('');
     WHILE @intStartIndexID <= @intEndIndexID
     BEGIN
         INSERT INTO @tblCodeChars (CodeChar, CodeValue)
         VALUES (CHAR(@intStartIndexID), );

         SET @intStartIndexID = @intStartIndexID + ;
     END

     -- 初始化A-Z字母字符
     SELECT
         @intStartIndexID = ASCII('A'),
         @intEndIndexID = ASCII('Z');
     WHILE @intStartIndexID <= @intEndIndexID
     BEGIN
         INSERT INTO @tblCodeChars (CodeChar, CodeValue)
         VALUES (CHAR(@intStartIndexID), );

         SET @intStartIndexID = @intStartIndexID + ;
     END

     -- 修改每个编码字符对应的编码值
     ;WITH tCodeData AS (
         SELECT CodeChar, ROW_NUMBER() OVER (ORDER BY CodeChar ASC) AS RowNum
         FROM @tblCodeChars
     )

     UPDATE T2
     SET T2.CodeValue = T.RowNum -
     FROM tCodeData AS T
         INNER JOIN @tblCodeChars AS T2
             ON T.CodeChar = T2.CodeChar;

     RETURN;
 END
 GO

设置编码字符固定长度的标量函数

 

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

 IF OBJECT_ID(N'dbo.ufn_GetCodeCharFixLength', 'FN') IS NOT NULL
 BEGIN
     DROP FUNCTION dbo.ufn_GetCodeCharFixLength;
 END
 GO

 --==================================
 -- 功能: 获取编码字符组合的固定长度
 -- 说明: 如果转化为int数据类，只能是8位整数，且字符串编码的固定长度只能是8，仅支持5到8位编码字符的组合
 -- 作者: XXX
 -- 创建: yyyy-MM-dd
 -- 修改: yyyy-MM-dd XXX 修改内容描述
 -- 调用: SELECT dbo.ufn_GetCodeCharFixLength();
 --==================================
 CREATE FUNCTION ufn_GetCodeCharFixLength
 (
 ) RETURNS TINYINT
     --$Encode$--
 AS
 BEGIN
     RETURN CAST( AS TINYINT);
 END
 GO

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

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

 IF OBJECT_ID(N'dbo.ufn_GetCodeIntegerValue', 'FN') IS NOT NULL
 BEGIN
     DROP FUNCTION dbo.ufn_GetCodeIntegerValue;
 END
 GO

 --==================================
 -- 功能: 通过编码字符获取其对应的整数数值
 -- 说明: 具体实现阐述
 -- 作者: XXX
 -- 创建: yyyy-MM-dd
 -- 修改: yyyy-MM-dd XXX 修改内容描述
 -- 调用: SELECT dbo.ufn_GetCodeIntegerValue('A0000')
 --==================================
 CREATE FUNCTION dbo.ufn_GetCodeIntegerValue
 (
     @chCodeChar CHAR()

 ) RETURNS INT
     --$Encode$--
 AS
 BEGIN
     SET @chCodeChar = ISNULL(@chCodeChar, '');
     SET @chCodeChar = UPPER(@chCodeChar);
     DECLARE @intCodeIntegerValue AS INT;
     SET @intCodeIntegerValue = ;

     DECLARE @tintFixLength AS TINYINT;
     SET @tintFixLength =dbo.ufn_GetCodeCharFixLength();

     DECLARE @tintLength AS TINYINT;
     SET @tintLength = LEN(@chCodeChar);

     IF @tintLength <= (@tintFixLength - ) OR @tintLength >= (@tintFixLength + )
     BEGIN
         RETURN @intCodeIntegerValue;
     END

     DECLARE @tblCodeChars TABLE(
         CodeChar CHAR() NOT NULL,
         CodeValue TINYINT NOT NULL
     );

     INSERT INTO  @tblCodeChars (CodeChar, CodeValue)
     SELECT CodeChar, CodeValue
     FROM dbo.ufn_GetCodeChars();

     WHILE @tintLength >=
     BEGIN
         SELECT @intCodeIntegerValue = @intCodeIntegerValue + CodeValue * POWER(, @tintFixLength - @tintLength)
         FROM @tblCodeChars
         WHERE CodeChar = SUBSTRING(@chCodeChar, @tintLength, );

         SET @tintLength = @tintLength - ;
     END

     RETURN @intCodeIntegerValue;
 END
 GO

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

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

 IF OBJECT_ID(N'dbo.ufn_GetCodeChar', 'FN') IS NOT NULL
 BEGIN
     DROP FUNCTION dbo.ufn_GetCodeChar;
 END
 GO

 --==================================
 -- 功能: 通过编码整数值获取对应的编码字符
 -- 说明: 具体实现阐述
 -- 作者: XXX
 -- 创建: yyyy-MM-dd
 -- 修改: yyyy-MM-dd XXX 修改内容描述
 --==================================
 CREATE FUNCTION dbo.ufn_GetCodeChar
 (
     @intCodeIntegerValue INT
 ) RETURNS CHAR()
     --$Encode$--
 AS
 BEGIN
     SET @intCodeIntegerValue = ISNULL(@intCodeIntegerValue, );
     DECLARE @chCodeChar AS VARCHAR();
     SET @chCodeChar = '';

     DECLARE @tintFixLength AS TINYINT;
     SET @tintFixLength =dbo.ufn_GetCodeCharFixLength();

     IF @intCodeIntegerValue NOT BETWEEN dbo.ufn_GetCodeIntegerValue('A' + REPLICATE('', @tintFixLength - )) AND dbo.ufn_GetCodeIntegerValue(REPLICATE('Z', @tintFixLength))
     BEGIN
         RETURN @chCodeChar;
     END

     DECLARE @tblCodeChars TABLE(
         CodeChar CHAR() NOT NULL,
         CodeValue TINYINT NOT NULL
     );

     INSERT INTO  @tblCodeChars (CodeChar, CodeValue)
     SELECT CodeChar , CodeValue
     FROM dbo.ufn_GetCodeChars();

     DECLARE @tintPerCodeValue TINYINT;
     SET @tintPerCodeValue = ;

     WHILE @tintFixLength >=
     BEGIN
         SET @tintPerCodeValue =  @intCodeIntegerValue / POWER(, @tintFixLength - );

         SELECT TOP  @chCodeChar = @chCodeChar + CodeChar, @tintPerCodeValue = CodeValue
         FROM @tblCodeChars
         WHERE CodeValue <= @tintPerCodeValue
         ORDER BY CodeValue DESC;

         SET @intCodeIntegerValue = @intCodeIntegerValue - @tintPerCodeValue * POWER(, @tintFixLength - );

         SET @tintFixLength = @tintFixLength - ;
     END

     RETURN @chCodeChar;
 END
 GO
  

测试实现效果

 

测试的T-SQL代码如下：

 DECLARE @chCodeChar AS CHAR();
 SET @chCodeChar = 'A0000';
 DECLARE @intValue AS INT;
 SET @intValue = dbo.ufn_GetCodeIntegerValue(@chCodeChar);

 SELECT @chCodeChar AS CurrentCodeChar, @intValue AS CurrentCodeIntegerValue, dbo.ufn_GetCodeChar(@intValue + ) AS NextCodeChar;
 GO

 DECLARE @chCodeChar AS CHAR();
 SET @chCodeChar = 'ZZZZY';
 DECLARE @intValue AS INT;
 SET @intValue = dbo.ufn_GetCodeIntegerValue(@chCodeChar);

 SELECT @chCodeChar AS CurrentCodeChar, @intValue AS CurrentCodeIntegerValue, dbo.ufn_GetCodeChar(@intValue + ) AS NextCodeChar;
 GO

执行后的查询结果如下：

实现方案的限制

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

 

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

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