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 -- 作者: XXX 12 -- 创建: yyyy-MM-dd 13 -- 修改: yyyy-MM-dd XXX 修改内容描述 14 -- 调用: SELECT CodeChar, CodeValue FROM dbo.ufn_GetCodeChars(); 15 --================================== 16 CREATE FUNCTION dbo.ufn_GetCodeChars 17 ( 18 ) RETURNS @tblCodeChars TABLE ( 19 CodeChar CHAR(1) NOT NULL, 20 CodeValue TINYINT NOT NULL 21 ) 22 --$Encode$-- 23 AS 24 BEGIN 25 DECLARE 26 @intStartIndexID AS TINYINT, 27 @intEndIndexID AS TINYINT; 28 29 SELECT 30 @intStartIndexID = 0, 31 @intEndIndexID = 0; 32 33 -- 初始化0-9数字字符 34 SELECT 35 @intStartIndexID = ASCII('0'), 36 @intEndIndexID = ASCII('9'); 37 WHILE @intStartIndexID <= @intEndIndexID 38 BEGIN 39 INSERT INTO @tblCodeChars (CodeChar, CodeValue) 40 VALUES (CHAR(@intStartIndexID), 0); 41 42 SET @intStartIndexID = @intStartIndexID + 1; 43 END 44 45 -- 初始化A-Z字母字符 46 SELECT 47 @intStartIndexID = ASCII('A'), 48 @intEndIndexID = ASCII('Z'); 49 WHILE @intStartIndexID <= @intEndIndexID 50 BEGIN 51 INSERT INTO @tblCodeChars (CodeChar, CodeValue) 52 VALUES (CHAR(@intStartIndexID), 0); 53 54 SET @intStartIndexID = @intStartIndexID + 1; 55 END 56 57 -- 修改每个编码字符对应的编码值 58 ;WITH tCodeData AS ( 59 SELECT CodeChar, ROW_NUMBER() OVER (ORDER BY CodeChar ASC) AS RowNum 60 FROM @tblCodeChars 61 ) 62 63 UPDATE T2 64 SET T2.CodeValue = T.RowNum - 1 65 FROM tCodeData AS T 66 INNER JOIN @tblCodeChars AS T2 67 ON T.CodeChar = T2.CodeChar; 68 69 RETURN; 70 END 71 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 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 -- 调用: SELECT dbo.ufn_GetCodeCharFixLength(); 14 --================================== 15 CREATE FUNCTION ufn_GetCodeCharFixLength 16 ( 17 ) RETURNS TINYINT 18 --$Encode$-- 19 AS 20 BEGIN 21 RETURN CAST(5 AS TINYINT); 22 END 23 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 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 -- 调用: SELECT dbo.ufn_GetCodeIntegerValue('A0000') 14 --================================== 15 CREATE FUNCTION dbo.ufn_GetCodeIntegerValue 16 ( 17 @chCodeChar CHAR(5) 18 19 ) RETURNS INT 20 --$Encode$-- 21 AS 22 BEGIN 23 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 BEGIN 36 RETURN @intCodeIntegerValue; 37 END 38 39 DECLARE @tblCodeChars TABLE( 40 CodeChar CHAR(1) NOT NULL, 41 CodeValue TINYINT NOT NULL 42 ); 43 44 INSERT INTO @tblCodeChars (CodeChar, CodeValue) 45 SELECT CodeChar, CodeValue 46 FROM dbo.ufn_GetCodeChars(); 47 48 WHILE @tintLength >= 1 49 BEGIN 50 SELECT @intCodeIntegerValue = @intCodeIntegerValue + CodeValue * POWER(10, @tintFixLength - @tintLength) 51 FROM @tblCodeChars 52 WHERE CodeChar = SUBSTRING(@chCodeChar, @tintLength, 1); 53 54 SET @tintLength = @tintLength - 1; 55 END 56 57 RETURN @intCodeIntegerValue; 58 END 59 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 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 --================================== 14 CREATE FUNCTION dbo.ufn_GetCodeChar 15 ( 16 @intCodeIntegerValue INT 17 ) RETURNS CHAR(5) 18 --$Encode$-- 19 AS 20 BEGIN 21 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 BEGIN 30 RETURN @chCodeChar; 31 END 32 33 DECLARE @tblCodeChars TABLE( 34 CodeChar CHAR(1) NOT NULL, 35 CodeValue TINYINT NOT NULL 36 ); 37 38 INSERT INTO @tblCodeChars (CodeChar, CodeValue) 39 SELECT CodeChar , CodeValue 40 FROM dbo.ufn_GetCodeChars(); 41 42 DECLARE @tintPerCodeValue TINYINT; 43 SET @tintPerCodeValue = 0; 44 45 WHILE @tintFixLength >= 1 46 BEGIN 47 SET @tintPerCodeValue = @intCodeIntegerValue / POWER(10, @tintFixLength - 1); 48 49 SELECT TOP 1 @chCodeChar = @chCodeChar + CodeChar, @tintPerCodeValue = CodeValue 50 FROM @tblCodeChars 51 WHERE CodeValue <= @tintPerCodeValue 52 ORDER BY CodeValue DESC; 53 54 SET @intCodeIntegerValue = @intCodeIntegerValue - @tintPerCodeValue * POWER(10, @tintFixLength - 1); 55 56 SET @tintFixLength = @tintFixLength - 1; 57 END 58 59 RETURN @chCodeChar; 60 END 61 GO 62
测试实现效果
测试的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。
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。
