SQL Server获取下一个编码字符串的实现方案分割和进位

我在前一种解决方案SQL Server获取下一个编码字符实现和后一种解决方案SQL Server获取下一个编码字符实现继续重构与增强两篇博文中均提供了一种解决编码的方案，考虑良久对比以上两种方案的，后一种方案虽然解决了其中方案的缺点，但是依然存在的编码字符串长度的限制（最多满足8位长度），本博文提供的方案将编码字符串长度增加到19位，也可以足够项目中实现这些编码。

具体的编码规则可以参看以上两种解决方案博文中的描述，也可以进入SQL Server 大V潇湘隐者的获取下一个编码字符串问题这篇博文。

这次实现的思路主要是分割和进位。

1、分割，是指将编码字符串分割为两部分：字母字符串和数字字符串。

2、有数字字符串存在的情况则进位，是指将数字1和数字字符串连接成新数字字符串，将新数字字符串转化为bigint整数。

如果该结果中的第一个数字为2，则表示该编码字符串要进位，这种的进位要分两种情况：字母字符串最后一个字母字符是否为"Z"，如果为”Z“，那数字字符串第一位数字变化为”A"，其余的数字字符串全部变为"0"；如果不为”Z"，那么字母字符串最后一个字母字符递增，数字字符串对应的整数值递增。

如果该结果中的第一个数字为1，则表示数字字符串部分对应的整数值递增。

在组装字母字符串和数字字符串就可以得到下一个的编码字符串了。

3、没有数字字符串的情况，则将最后字母字符串最后一位字母字符递增组成新的编码字符串。

补充修改和优化

　　有关以下两个实现方案中针对从编码字符串中获取首个字母字符位置的方法使用了普通的循序方式，基于编码字符规则的定义，可以通过函数PATINDEX来实现，使用该函数的T-SQL代码如下：

 1     -- 找到到首个数字字符的位置(其所在编码字符串中的位置)
 2     -- 方式1：通过循环获得
 3     --WHILE @tintFistNumPos <= @tintLength
 4     --BEGIN
 5     --    SET @tintCharASCIIValue = ASCII(SUBSTRING(@chvCodeChars, @tintFistNumPos, 1));
 6     --    IF @tintCharASCIIValue BETWEEN ASCII('0') AND ASCII('9')
 7     --    BEGIN
 8     --        BREAK;
 9     --    END
10 
11     --    SET @tintFistNumPos = @tintFistNumPos + 1;
12     --END  
13 
14     ---- 分割编码字符串到字母字符串和数字字符串
15     --SET @chvLetterChars = SUBSTRING(@chvCodeChars, 1, @tintFistNumPos - 1);
16     ---- 只有找到数字字时才分割获得数字字符串
17     --IF @tintFistNumPos <= @tintLength
18     --BEGIN
19     --    SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos + 1);
20     --END
21       
22     -- 方法2：通过PATINDEX函数
23     SET @tintFistNumPos = PATINDEX('%[0-9]%', @chvCodeChars);
24     IF @tintFistNumPos BETWEEN 2 AND @tintLength            -- 编码字符串规则，首字符必须是字母，只有第2个字符才可为数字。
25     BEGIN
26         -- 分割编码字符串得到字母字符串
27         SET @chvLetterChars = SUBSTRING(@chvCodeChars, 1, @tintFistNumPos - 1);
28         -- 分割编码字符串得到数字字符串
29         SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos + 1);
30     END
31     ELSE IF @tintFistNumPos = 0                                -- 表示该编码字符串全部为字母字符    
32     BEGIN
33         SET @chvLetterChars = @chvCodeChars;
34     END

实现方案代码

该方案的T-SQL代码如下：

  1 IF OBJECT_ID(N'dbo.ufn_GetNextCodeChars', 'FN') IS NOT NULL
  2 BEGIN
  3     DROP FUNCTION dbo.ufn_GetNextCodeChars;
  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_GetNextCodeChars
 15 (
 16     @chvCodeChars VARCHAR(19)            -- 编码字符串,首字符必须以字母A-Z任意一个开始。
 17 ) RETURNS VARCHAR(19)
 18     --$Encode$--
 19 AS
 20 BEGIN;
 21     SET @chvCodeChars = ISNULL(@chvCodeChars, '');
 22     SET @chvCodeChars = UPPER(@chvCodeChars);
 23 
 24     -- 下一个编码字符串变量
 25     DECLARE @chvNextCodeChars AS VARCHAR(19);
 26     SET @chvNextCodeChars = '';
 27 
 28     -- 编码字符使用的字符字符串变量
 29     DECLARE @chCharStr AS CHAR(36);
 30     SET @chCharStr = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
 31 
 32     DECLARE 
 33         @tintLength AS TINYINT,                
 34         @tintFistNumPos AS TINYINT;
 35     SELECT
 36         @tintLength = LEN(@chvCodeChars),    -- 编码字符串长度变量    
 37         @tintFistNumPos = 2;                -- 首个数字字符所在位置的变量，默认第二个字符是数字字符
 38 
 39     DECLARE
 40         @chvLetterChars AS VARCHAR(19),     -- 字母字符串
 41         @chvNumChars AS VARCHAR(19);        -- 数字字符串
 42     SELECT
 43         @chvLetterChars = '',
 44         @chvNumChars = '';
 45 
 46     -- 字符ASCII值变量    
 47     DECLARE @tintCharASCIIValue AS TINYINT;
 48     SET @tintCharASCIIValue = 0;
 49 
 50     -- 编码字符串长度的逻辑检查
 51     IF @tintLength NOT BETWEEN 1 AND 19
 52     BEGIN
 53         RETURN @chvNextCodeChars;
 54     END
 55 
 56     -- 首字符是否字母字符的逻辑检查
 57     SET @tintCharASCIIValue = ASCII(SUBSTRING(@chvCodeChars, 1, 1));
 58     IF @tintCharASCIIValue NOT BETWEEN ASCII('A') AND ASCII('Z')
 59     BEGIN
 60         RETURN @chvNextCodeChars;
 61     END
 62 
 63     -- 所有字符全部为'Z'的逻辑检查
 64     IF @chvCodeChars = REPLICATE('Z', @tintLength)
 65     BEGIN
 66         RETURN @chvNextCodeChars;
 67     END
 68 
 69     -- 找到到首个数字字符的位置(其所在编码字符串中的位置)
 70     -- 方式1：通过循环获得
 71     --WHILE @tintFistNumPos <= @tintLength
 72     --BEGIN
 73     --    SET @tintCharASCIIValue = ASCII(SUBSTRING(@chvCodeChars, @tintFistNumPos, 1));
 74     --    IF @tintCharASCIIValue BETWEEN ASCII('0') AND ASCII('9')
 75     --    BEGIN
 76     --        BREAK;
 77     --    END
 78 
 79     --    SET @tintFistNumPos = @tintFistNumPos + 1;
 80     --END  
 81 
 82     ---- 分割编码字符串到字母字符串和数字字符串
 83     --SET @chvLetterChars = SUBSTRING(@chvCodeChars, 1, @tintFistNumPos - 1);
 84     ---- 只有找到数字字时才分割获得数字字符串
 85     --IF @tintFistNumPos <= @tintLength
 86     --BEGIN
 87     --    SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos + 1);
 88     --END
 89       
 90     -- 方法2：通过PATINDEX函数
 91     SET @tintFistNumPos = PATINDEX('%[0-9]%', @chvCodeChars);
 92     IF @tintFistNumPos BETWEEN 2 AND @tintLength            -- 编码字符串规则，首字符必须是字母，只有第2个字符才可为数字。
 93     BEGIN
 94         -- 分割编码字符串得到字母字符串
 95         SET @chvLetterChars = SUBSTRING(@chvCodeChars, 1, @tintFistNumPos - 1);
 96         -- 分割编码字符串得到数字字符串
 97         SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos + 1);
 98     END
 99     ELSE IF @tintFistNumPos = 0                                -- 表示该编码字符串全部为字母字符    
100     BEGIN
101         SET @chvLetterChars = @chvCodeChars;
102     END
103 
104     -- 字母字符串长度和字母字符串最后一个字母
105     DECLARE
106         @tintLetterLength AS TINYINT,
107         @chLastLetter AS CHAR(1);
108     SELECT
109         @tintLetterLength = LEN(@chvLetterChars),
110         @chLastLetter = SUBSTRING(@chvLetterChars, @tintLetterLength, 1);
111 
112     IF LEN(@chvNumChars) = 0        /*最后一位不为Z或是数字字符时的逻辑处理*/
113     BEGIN
114         SET @chvLetterChars = SUBSTRING(@chvLetterChars, 1, @tintLetterLength - 1)
115                               + SUBSTRING(@chCharStr, CHARINDEX(@chLastLetter, @chCharStr, 1) + 1, 1);
116     END
117     ELSE                    /*数字字符超过1位(最多18位)时逻辑处理*/
118     BEGIN
119         -- 声明一个特殊的整数变量，开始为“1”后边紧跟数字字符串，在转为整数进行加法运算，如果该结果首字符从1变成了2，则表示前面相邻的字母字符需要递进增加；否则只是数字字符串进行递进增加。
120         DECLARE @bintNumPlusOne AS BIGINT;
121         SET @bintNumPlusOne = CAST('1' + + @chvNumChars AS BIGINT) + 1;
122 
123         IF SUBSTRING(CAST(@bintNumPlusOne AS VARCHAR(19)), 1, 1) = '2' /*数字字符串全部为9*/
124         BEGIN 
125             IF @chLastLetter = 'Z' /*如果数字字符串相邻前面字母为'Z',则第一个数字变为'A'，其余的数字字符串全部变为0*/
126             BEGIN
127                 SET @chvNumChars = 'A' + REPLICATE('0', LEN(@chvNumChars) - 1);
128             END
129             ELSE                            /*如果数字字符串相邻前面字母不为'Z',则这个字母递进增加，数字字符串全部变为0*/
130             BEGIN
131                 SET @chvLetterChars = SUBSTRING(@chvLetterChars, 1, @tintLetterLength - 1)
132                                         + SUBSTRING(@chCharStr, CHARINDEX(@chLastLetter, @chCharStr, 1) + 1, 1);
133 
134                 SET @chvNumChars = REPLICATE('0', LEN(CAST(@bintNumPlusOne AS VARCHAR(19))) - 1);
135             END       
136         END
137         ELSE                                                            /*数字字符串第一个数字字符不为9，其余的数字字符可全部为9*/
138         BEGIN
139             SET @chvNumChars = STUFF(CAST(@bintNumPlusOne AS VARCHAR(19)), 1, 1, '');
140         END  
141     END
142 
143     -- 将字母字符串和数字字符串一起组装成下一个编码字符串
144     SET @chvNextCodeChars = @chvLetterChars + @chvNumChars;
145 
146     RETURN @chvNextCodeChars;
147 END
148 GO

实现方案效果

测试实现方案的T-SQL代码如下：

 1 DECLARE @chvCodeChars AS VARCHAR(19);
 2  
 3 SET @chvCodeChars = 'ZZZZZZZZZZZZZZZZZ99' 
 4 SELECT @chvCodeChars AS [当前编码字符串], dbo.ufn_GetNextCodeChars(@chvCodeChars) AS [相邻前面字母为Z且字母进位];
 5  
 6 SET @chvCodeChars = 'AAAA99';
 7 SELECT @chvCodeChars AS [当前编码字符串], dbo.ufn_GetNextCodeChars(@chvCodeChars) AS [相邻前面字母不为Z且字母进位];
 8  
 9 SET @chvCodeChars = 'ZZZZZZZZZZZZZZZZA99';
10 SELECT @chvCodeChars AS [当前编码字符串], dbo.ufn_GetNextCodeChars(@chvCodeChars) AS [相邻前面字母不为Z且字母进位];
11  
12 SET @chvCodeChars = 'ZZZZZZZZZZZZZZZZB00';
13 SELECT @chvCodeChars AS [当前编码字符串], dbo.ufn_GetNextCodeChars(@chvCodeChars) AS [数字进位];
14  
15 SET @chvCodeChars = 'ZZZZZZZZZZZZZZZZZA';
16 SELECT @chvCodeChars AS [当前编码字符串], dbo.ufn_GetNextCodeChars(@chvCodeChars) AS [全为字母且字母进位];
17 GO

执行后的查询结果如下：

补充的解决方案

根据博友KingJaja提供的解决方案，该方案针对边界的判断很简洁，需要调整支持19位长度编码字符以及以“9"结尾且长度小于范围值长度时的小bug，针对以上的增强和修改后的的T-SQL脚本代码如下：

  1 IF OBJECT_ID(N'[dbo].[ufn_GenerateNexCodeChars]', 'FN') IS NOT NULL
  2 BEGIN
  3     DROP FUNCTION [dbo].[ufn_GenerateNexCodeChars];
  4 END
  5 GO
  6 
  7 CREATE FUNCTION [dbo].[ufn_GenerateNexCodeChars]
  8 (   
  9     @chvCodeChars VARCHAR(19)
 10 ) RETURNS VARCHAR(19)
 11 AS
 12 BEGIN
 13     SET @chvCodeChars = ISNULL(@chvCodeChars, '');
 14     SET @chvCodeChars = UPPER(@chvCodeChars);
 15 
 16     DECLARE @chvNextCodeChars AS VARCHAR(19);
 17     SET @chvNextCodeChars = '';
 18 
 19      DECLARE 
 20         @tintLength AS TINYINT,                -- 编码字符串长度           
 21         @tintFistNumPos AS TINYINT,            -- 编码字符串中第一个数字字符的位置,默认为第2个位置
 22         @chvLetterChars AS VARCHAR(19),     -- 字母字符串
 23         @chvNumChars AS VARCHAR(18),        -- 数字字符串
 24         @tintCharASCIIValue AS TINYINT;        -- 字符ASCII整数值
 25     SELECT
 26         @tintLength = LEN(@chvCodeChars),  
 27         @tintFistNumPos = 2,
 28         @chvLetterChars = '',
 29         @chvNumChars = '',
 30         @tintCharASCIIValue = 0;
 31 
 32     -- 编码字符串长度的逻辑检查
 33     IF @tintLength NOT BETWEEN 1 AND 19
 34     BEGIN
 35         RETURN @chvNextCodeChars;
 36     END
 37 
 38     -- 所有字符全部为'Z'的逻辑检查
 39     IF @chvCodeChars = REPLICATE('Z', @tintLength)
 40     BEGIN
 41         RETURN @chvNextCodeChars;
 42     END
 43 
 44     -- 首字符是否字母字符的逻辑检查
 45     SET @tintCharASCIIValue = ASCII(SUBSTRING(@chvCodeChars, 1, 1));
 46     IF @tintCharASCIIValue NOT BETWEEN ASCII('A') AND ASCII('Z')
 47     BEGIN
 48         RETURN @chvNextCodeChars;
 49     END
 50 
 51     -- 找到到首个数字字符的位置(其所在编码字符串中的位置)
 52     -- 方式1：通过循环获得
 53     --WHILE @tintFistNumPos <= @tintLength
 54     --BEGIN
 55     --    SET @tintCharASCIIValue = ASCII(SUBSTRING(@chvCodeChars, @tintFistNumPos, 1));
 56     --    IF @tintCharASCIIValue BETWEEN ASCII('0') AND ASCII('9')
 57     --    BEGIN
 58     --        BREAK;
 59     --    END
 60 
 61     --    SET @tintFistNumPos = @tintFistNumPos + 1;
 62     --END  
 63 
 64     ---- 分割编码字符串到字母字符串和数字字符串
 65     --SET @chvLetterChars = SUBSTRING(@chvCodeChars, 1, @tintFistNumPos - 1);
 66     ---- 只有找到数字字符时才分割获得数字字符串
 67     --IF @tintFistNumPos <= @tintLength
 68     --BEGIN
 69     --    SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos + 1);
 70     --END
 71       
 72     -- 方法2：通过PATINDEX函数
 73     SET @tintFistNumPos = PATINDEX('%[0-9]%', @chvCodeChars);
 74     IF @tintFistNumPos BETWEEN 2 AND @tintLength            -- 编码字符串规则，首字符必须是字母，只有第2个字符才可为数字。
 75     BEGIN
 76         -- 分割编码字符串得到字母字符串
 77         SET @chvLetterChars = SUBSTRING(@chvCodeChars, 1, @tintFistNumPos - 1);
 78         -- 分割编码字符串得到数字字符串
 79         SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos + 1);
 80     END
 81     ELSE IF @tintFistNumPos = 0                                -- 表示该编码字符串全部为字母字符    
 82     BEGIN
 83         SET @chvLetterChars = @chvCodeChars;
 84     END
 85 
 86     DECLARE
 87         @tintLetterCharsLength AS TINYINT,
 88         @tintNumCharsLength AS TINYINT,
 89         @chLastLetterOfLetterChars AS CHAR(1);
 90     SELECT
 91         @tintLetterCharsLength = LEN(@chvLetterChars),
 92         @tintNumCharsLength = LEN(@chvNumChars),
 93         @chLastLetterOfLetterChars = SUBSTRING(@chvLetterChars, @tintLetterCharsLength, 1);
 94 
 95     IF @chvNumChars = REPLICATE('9', @tintNumCharsLength)                                -- 当编码字符串需要数字字符串部分进位时，即数字字符串全部为9或空字符串（不是NULL，而是'')
 96     BEGIN
 97         IF @chLastLetterOfLetterChars = 'Z'                                                -- 字母字符串最后一个字母字符是'Z'
 98         BEGIN
 99             SET @chvLetterChars = @chvLetterChars + 'A';
100             SET @chvNumChars =REPLICATE('0', @tintNumCharsLength - 1);
101         END
102         ELSE                                                                            -- 字母字符串最后一个字母字符不是'Z'，则进位该自字母字符
103         BEGIN
104             SET @chvLetterChars = SUBSTRING(@chvLetterChars, 1, @tintLetterCharsLength - 1) 
105                                   + CHAR(ASCII(@chLastLetterOfLetterChars) + 1);
106             SET @chvNumChars =REPLICATE('0', @tintNumCharsLength);
107         END        
108     END
109     ELSE                                                            
110     BEGIN
111          SET @chvNumChars = CAST(CAST(@chvNumChars AS BIGINT) + 1 AS VARCHAR(18))        -- 数字部分转换为bigint再递增1再转换为字符串
112          SET @chvNumChars = REPLICATE('0', @tintNumCharsLength - LEN(@chvNumChars)) 
113                             + @chvNumChars;                                                -- 数字字符串补充缺0     
114     END
115 
116     SET @chvNextCodeChars =@chvLetterChars+ @chvNumChars;                                -- 组装下一个编码字符串
117 
118     RETURN @chvNextCodeChars;     
119 END
120 GO

测试的T-SQL代码如下：

1 SELECT dbo.ufn_GenerateNexCodeChars('Z0')
2     ,dbo.ufn_GenerateNexCodeChars('Z9')
3     ,dbo.ufn_GenerateNexCodeChars('ZY')
4     ,dbo.ufn_GenerateNexCodeChars('ZA9');
5 GO

执行后的查询结果如下：

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

posted @ 2015-12-29 21:04 剑走江湖阅读(646) 评论(0) 编辑收藏举报

刷新页面返回顶部

剑走江湖

以空杯者心态生活和学习：愉悦生活、快乐学习。

SQL Server获取下一个编码字符串的实现方案分割和进位

公告