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 @tintASCIIValue AS TINYINT;
48 SET @tintASCIIValue =
0;
49
50 --
编码字符串长度的逻辑检查
51 IF @tintLength NOT BETWEEN
1 AND
19
52 BEGIN
53 RETURN @chvNextCodeChars;
54 END
55
56 --
首字符是否字母字符的逻辑检查
57 SET @tintASCIIValue = ASCII(SUBSTRING(@chvCodeChars,
1,
1));
58 IF @tintASCIIValue 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 WHILE @tintFistNumPos <=
@tintLength
71 BEGIN
72 SET @tintASCIIValue = ASCII(SUBSTRING(@chvCodeChars, @tintFistNumPos,
1));
73 IF @tintASCIIValue BETWEEN ASCII(
‘0‘) AND ASCII(
‘9‘)
74 BEGIN
75 BREAK;
76 END
77
78 SET @tintFistNumPos = @tintFistNumPos +
1;
79 END
80
81 --
分割编码字符串分别带到字母字符串和数字字符串
82 SET @chvLetterChars = SUBSTRING(@chvCodeChars,
1, @tintFistNumPos -
1);
83 --
只有找到数字字时才分割获得数字字符串
84 IF @tintFistNumPos <=
@tintLength
85 BEGIN
86 SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos +
1);
87 END
88
89 --
字母字符串长度和字母字符串最后一个字母
90 DECLARE
91 @tintLetterLength AS TINYINT,
92 @chLastLetter AS CHAR(
1);
93 SELECT
94 @tintLetterLength =
LEN(@chvLetterChars),
95 @chLastLetter = SUBSTRING(@chvLetterChars, @tintLetterLength,
1);
96
97 IF LEN(@chvNumChars) =
0 /*最后一位不为Z或是数字字符时的逻辑处理*/
98 BEGIN
99 SET @chvLetterChars = SUBSTRING(@chvLetterChars,
1, @tintLetterLength -
1)
100 + SUBSTRING(@chCharStr, CHARINDEX(@chLastLetter, @chCharStr,
1) +
1,
1);
101 END
102 ELSE
/*数字字符超过1位(最多18位)时逻辑处理*/
103 BEGIN
104 -- 声明一个特殊的整数变量，开始为“
1”后边紧跟数字字符串，在转为整数进行加法运算，如果该结果首字符从1变成了2，则表示前面相邻的字母字符需要递进增加；否则只是数字字符串进行递进增加。
105 DECLARE @bintNumPlusOne AS BIGINT;
106 SET @bintNumPlusOne = CAST(
‘1‘ + + @chvNumChars AS BIGINT) +
1;
107
108 IF SUBSTRING(CAST(@bintNumPlusOne AS VARCHAR(
19)),
1,
1) =
‘2‘ /*数字字符串全部为9*/
109 BEGIN
110 IF @chLastLetter =
‘Z‘ /*如果数字字符串相邻前面字母为‘Z‘,则第一个数字变为‘A‘，其余的数字字符串全部变为0*/
111 BEGIN
112 SET @chvNumChars =
‘A‘ + REPLICATE(
‘0‘, LEN(@chvNumChars) -
1);
113 END
114 ELSE
/*如果数字字符串相邻前面字母不为‘Z‘,则这个字母递进增加，数字字符串全部变为0*/
115 BEGIN
116 SET @chvLetterChars = SUBSTRING(@chvLetterChars,
1, @tintLetterLength -
1)
117 + SUBSTRING(@chCharStr, CHARINDEX(@chLastLetter, @chCharStr,
1) +
1,
1);
118
119 SET @chvNumChars = REPLICATE(
‘0‘, LEN(CAST(@bintNumPlusOne AS VARCHAR(
19))) -
1);
120 END
121 END
122 ELSE
/*数字字符串第一个数字字符不为9，其余的数字字符可全部为9*/
123 BEGIN
124 SET @chvNumChars = STUFF(CAST(@bintNumPlusOne AS VARCHAR(
19)),
1,
1,
‘‘);
125 END
126 END
127
128 --
将字母字符串和数字字符串一起组装成下一个编码字符串
129 SET @chvNextCodeChars = @chvLetterChars +
@chvNumChars;
130
131 RETURN @chvNextCodeChars;
132 END
133 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
执行后的查询结果如下：
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。
SQL Server获取下一个编码字符串的实现方案分割和进位
标签：
