我在前一种解决方案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 -- END10 11 -- SET @tintFistNumPos = @tintFistNumPos + 1;12 --END 13 14 ---- 分割编码字符串到字母字符串和数字字符串15 --SET @chvLetterChars = SUBSTRING(@chvCodeChars, 1, @tintFistNumPos - 1);16 ---- 只有找到数字字时才分割获得数字字符串17 --IF @tintFistNumPos <= @tintLength18 --BEGIN19 -- SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos + 1);20 --END21 22 -- 方法2：通过PATINDEX函数23 SET @tintFistNumPos = PATINDEX('%[0-9]%', @chvCodeChars);24 IF @tintFistNumPos BETWEEN 2 AND @tintLength -- 编码字符串规则，首字符必须是字母，只有第2个字符才可为数字。25 BEGIN26 -- 分割编码字符串得到字母字符串27 SET @chvLetterChars = SUBSTRING(@chvCodeChars, 1, @tintFistNumPos - 1);28 -- 分割编码字符串得到数字字符串29 SET @chvNumChars = SUBSTRING(@chvCodeChars, @tintFistNumPos, @tintLength - @tintFistNumPos + 1);30 END31 ELSE IF @tintFistNumPos = 0 -- 表示该编码字符串全部为字母字符 32 BEGIN33 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 BEGIN101 SET @chvLetterChars = @chvCodeChars;102 END103 104 -- 字母字符串长度和字母字符串最后一个字母105 DECLARE106 @tintLetterLength AS TINYINT,107 @chLastLetter AS CHAR(1);108 SELECT109 @tintLetterLength = LEN(@chvLetterChars),110 @chLastLetter = SUBSTRING(@chvLetterChars, @tintLetterLength, 1);111 112 IF LEN(@chvNumChars) = 0 /*最后一位不为Z或是数字字符时的逻辑处理*/113 BEGIN114 SET @chvLetterChars = SUBSTRING(@chvLetterChars, 1, @tintLetterLength - 1)115 + SUBSTRING(@chCharStr, CHARINDEX(@chLastLetter, @chCharStr, 1) + 1, 1);116 END117 ELSE /*数字字符超过1位(最多18位)时逻辑处理*/118 BEGIN119 -- 声明一个特殊的整数变量，开始为“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 BEGIN127 SET @chvNumChars = 'A' + REPLICATE('0', LEN(@chvNumChars) - 1);128 END129 ELSE /*如果数字字符串相邻前面字母不为'Z',则这个字母递进增加，数字字符串全部变为0*/130 BEGIN131 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 END137 ELSE /*数字字符串第一个数字字符不为9，其余的数字字符可全部为9*/138 BEGIN139 SET @chvNumChars = STUFF(CAST(@bintNumPlusOne AS VARCHAR(19)), 1, 1, '');140 END 141 END142 143 -- 将字母字符串和数字字符串一起组装成下一个编码字符串144 SET @chvNextCodeChars = @chvLetterChars + @chvNumChars;145 146 RETURN @chvNextCodeChars;147 END148 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 END102 ELSE -- 字母字符串最后一个字母字符不是'Z'，则进位该自字母字符103 BEGIN104 SET @chvLetterChars = SUBSTRING(@chvLetterChars, 1, @tintLetterCharsLength - 1) 105 + CHAR(ASCII(@chLastLetterOfLetterChars) + 1);106 SET @chvNumChars =REPLICATE('0', @tintNumCharsLength);107 END 108 END109 ELSE 110 BEGIN111 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 END115 116 SET @chvNextCodeChars [email protected]+ @chvNumChars; -- 组装下一个编码字符串117 118 RETURN @chvNextCodeChars; 119 END120 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
执行后的查询结果如下：
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。