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。
 
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。
 
 
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/dzy863/p/5083164.html