我在前一种解决方案 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
执行后的查询结果如下:
博友如有其他更好的解决方案,也请不吝赐教,万分感谢。