我正在使用 SQL Server 存储过程来生成具有以下模式的 10 位数字序列，其中[CustomerCode]说“ABC”：
[CustomerCode]0000001
...
[CustomerCode]9999999
[CustomerCode]A000001
...
[CustomerCode]Z999999
[CustomerCode]ZA00001
...
[CustomerCode]ZZ99999
[CustomerCode]ZZA0001
...
[CustomerCode]ZZZZZZZ
所以这个存储过程将接受一个字符串作为输入参数，并返回下一个值，例如它接受'ABCZ999999'并返回'ABCZA00001'。
我有以下存储过程和函数，但它们不能完全正常工作，而且我似乎无法正确使用。
任何帮助表示赞赏。
-- Function to increment numeric value
CREATE FUNCTION [dbo].[IncrementNumericValue]
(@CurrentValue Varchar(2))
RETURNS Varchar(2)
AS
BEGIN
DECLARE @RetValue Varchar(2)
IF (@CurrentValue < CAST(9 AS Varchar(2)))
BEGIN
SET @RetValue = CAST((CAST(@CurrentValue AS Int) + Cast(1 AS Int)) AS Varchar(2))
END
ELSE IF (@CurrentValue >= CAST(9 AS Varchar(2)))
BEGIN
SET @RetValue = CAST(0 AS Varchar(2))
END
ELSE
BEGIN
SET @RetValue = CAST('-' AS Varchar(2))
END
RETURN (@RetValue)
END
-- Function to increment alpha value
CREATE FUNCTION [dbo].[IncrementAlphaValue]
(@CurrentValue Varchar(2))
RETURNS Varchar(2)
AS
BEGIN
DECLARE @RetValue Varchar(2)
IF (@CurrentValue < CAST(9 AS Varchar(2)))
BEGIN
SET @RetValue = CAST((CAST(@CurrentValue AS Int) + CAST(1 AS Int)) AS Varchar(2))
END
ELSE IF (@CurrentValue = CAST(9 AS Varchar(2)))
BEGIN
SET @RetValue = CAST('A' As Varchar(2))
END
ELSE IF ((@CurrentValue >= CAST('A' AS Varchar(2)))
AND (@CurrentValue < CAST('Z' AS Varchar(2))))
BEGIN
SET @RetValue = CAST((Char((CAST(ASCII(@CurrentValue) AS Int) + CAST(1 AS Int)))) AS Varchar(2))
END
ELSE IF (@CurrentValue = CAST('Z' AS Varchar(2)))
BEGIN
SET @RetValue = CAST(0 AS Varchar(2))
END
ELSE
BEGIN
SET @RetValue = CAST('-' AS Varchar(2))
END
RETURN (@RetValue)
END
-- Function to calculate the next sequence of the pattern
CREATE PROCEDURE [dbo].[GetNextSequence]
@NewTarget Varchar(10) OUTPUT
AS
BEGIN
DECLARE @LastVariable Varchar(10)
DECLARE @PositionIndex Int
DECLARE @PassParameterWidth Int
DECLARE @TargetWidth Int
DECLARE @SelectedValue Varchar(2)
DECLARE @NewValue Varchar(2)
DECLARE @FinalValue Varchar(10)
DECLARE @ReplaceStringLength Int
DECLARE @ReplaceString Varchar(10)
DECLARE @NewCODE Varchar(10)
DECLARE @MaxTargetWidth Int
DECLARE @customerCode char(3)
DECLARE @NumberOfNine Int
DECLARE @AlphaNumericPartWidth Int
BEGIN
BEGIN
-- Input
SET @customerCode = 'ABC'
SELECT @LastVariable = 'ABCZ999999'
SET @PassParameterWidth = Len(@LastVariable)
SET @AlphaNumericPartWidth = @PassParameterWidth - Len(@customerCode)
SET @LastVariable = SUBSTRING(@LastVariable, 4, 7)
SET @TargetWidth = Cast(4 AS Int)
SET @MaxTargetWidth = Cast(7 AS Int)
SET @NewValue = ''
SET @FinalValue = ''
SET @ReplaceStringLength = Cast(0 AS Int)
SET @ReplaceString = ''
SET @NumberOfNine = 0
IF (@AlphaNumericPartWidth = CAST(7 AS Int))
BEGIN
SET @PositionIndex = @AlphaNumericPartWidth
WHILE(@PositionIndex > 0)
BEGIN
SET @SelectedValue = SUBSTRING(@LastVariable, @PositionIndex, 1)
IF (@SelectedValue LIKE '[A-Z]') -- Increment alpha
SET @NewValue = dbo.IncrementAlphaValue(@SelectedValue)
ELSE -- Increment numeric
SET @NewValue = dbo.IncrementNumericValue(@SelectedValue)
-- Right most digit
IF (@PositionIndex = 1)
BEGIN
IF (@SelectedValue = 'Z')
BEGIN
-- If left of 'Z' all '9' replace with 'ZA'
IF (LEN(REPLACE(PARSENAME(REPLACE(@LastVariable, 'Z', '.'), 1), '9','')) = 0)
BEGIN
SET @LastVariable = REPLACE(@LastVariable, 'Z', 'ZA');
SET @LastVariable = REPLACE(@LastVariable, '9', '0');
RETURN
END
END
-- If prefixed with letter or All '9', increment alpha
IF (@NewValue LIKE '[A-Y]' OR LEN(REPLACE(@LastVariable, '9','')) = 0)
BEGIN
SET @NewValue = dbo.IncrementAlphaValue(@SelectedValue)
END
END
SET @FinalValue = @NewValue + @FinalValue
-- Break is it's 1
IF (@NewValue <> 9)
BREAK
-- Move position to left
SET @PositionIndex = @PositionIndex - Cast(1 As Int)
END
-- Check Final value length
IF (LEN(@FinalValue) <= @MaxTargetWidth)
BEGIN
SET @LastVariable = @customerCode + @LastVariable
-- Get replace string length
SET @ReplaceStringLength = @PassParameterWidth - Len(@FinalValue)
-- Get replace string
SET @ReplaceString = LEFT(@LastVariable, @ReplaceStringLength)
-- NEW CODE
SET @NewCODE = @ReplaceString + @FinalValue
SET @NewTarget = @NewCODE
END
ELSE
BEGIN
SET @NewTarget = 'ERROR1'
END
END
ELSE
BEGIN
SET @NewTarget = 'ERROR222'
END
END
END
END
像这样的事情应该可以解决问题：
--declare @test varchar(10) = 'ABCZZ99999';
--declare @test varchar(10) = 'ABC0000001';
declare @test varchar(10) = 'ABCZZZZZZG';
--declare @test varchar(10) = 'ABC9999999';
--declare @test varchar(10) = 'ABCA000001';
--declare @test varchar(10) = 'ABCE999999';
--declare @test varchar(10) = 'ABCZ999999';
--declare @test varchar(10) = 'ABCZG99999';
--declare @test varchar(10) = 'ABCZA00001';
--declare @test varchar(10) = 'ABCZZ99999';
declare @idpart varchar(7) = SUBSTRING(@test, 4, 7);
declare @custpart varchar(7) = SUBSTRING(@test, 1, 3);
declare @numpos int = (SELECT PATINDEX('%[0-9]%', @idpart));
declare @numpart varchar(7);
declare @letterpart varchar(7);
if @numpos > 0
BEGIN
SET @numpart = SUBSTRING(@idpart, @numpos, 8 - @numpos);
SET @letterpart = SUBSTRING(@idpart, 1, @numpos - 1);
END
ELSE
BEGIN
SET @numpart = '';
SET @letterpart = @idpart;
END
declare @newnumpart varchar(7);
declare @newletterpart varchar(7);
IF @numpart = ''
BEGIN
SET @newletterpart = (SELECT REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE(@letterpart, 'Y', 'Z'),
'X', 'Y'),
'W', 'X'),
'V', 'W'),
'U', 'V'),
'T', 'U'),
'S', 'T'),
'R', 'S'),
'Q', 'R'),
'P', 'Q'),
'O', 'P'),
'N', 'O'),
'M', 'N'),
'L', 'M'),
'K', 'L'),
'J', 'K'),
'I', 'J'),
'H', 'I'),
'G', 'H'),
'F', 'G'),
'E', 'F'),
'D', 'E'),
'C', 'D'),
'B', 'C'),
'A', 'B'));
SET @newnumpart = '';
END
ELSE
BEGIN
declare @non9 int = (SELECT PATINDEX('%[0-8]%', @numpart));
IF @non9 > 0
BEGIN
--Number part is not all 9s so we can cast as int add 1 and cast back to varchar
declare @numint int = (SELECT CAST(@numpart as int) + 1);
declare @numstr varchar(7) = (SELECT CAST(@numint as varchar(7)));
SET @newnumpart = (SELECT REPLICATE('0', LEN(@numpart) - LEN(@numstr)) + @numstr);
SET @newletterpart = @letterpart;
END
ELSE
IF @letterpart = ''
BEGIN
SET @newnumpart = '000001';
SET @newletterpart = 'A';
END
ELSE
BEGIN
declare @nonZ int = (SELECT PATINDEX('%[A-Y]%', @letterpart));
IF @nonZ > 0
BEGIN
SET @newletterpart = (SELECT REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE(@letterpart, 'Y', 'Z'),
'X', 'Y'),
'W', 'X'),
'V', 'W'),
'U', 'V'),
'T', 'U'),
'S', 'T'),
'R', 'S'),
'Q', 'R'),
'P', 'Q'),
'O', 'P'),
'N', 'O'),
'M', 'N'),
'L', 'M'),
'K', 'L'),
'J', 'K'),
'I', 'J'),
'H', 'I'),
'G', 'H'),
'F', 'G'),
'E', 'F'),
'D', 'E'),
'C', 'D'),
'B', 'C'),
'A', 'B'));
SET @newnumpart = (SELECT REPLICATE('0', LEN(@numpart) - 1) + '1');
END
ELSE
BEGIN
SET @newletterpart = @letterpart + 'A';
SET @newnumpart = (SELECT REPLICATE('0', LEN(@numpart) - 2) + '1');
END
END
END
SELECT @custpart + @newletterpart + @newnumpart;
显然，我只是将它作为一个直接查询来完成，但是从中生成一个存储过程是微不足道的。两个关键的测试用例是数字元素是否全部为 9，字母元素是否全部为 Z。如果数字元素不全是 9，那么我们通过转换为 int 加 1 并使用REPLICATE填充零转换回 varchar 来获得下一个数字部分。如果数字元素全为零，那么第二个关键案例就出现了。我们是否有一个可以递增的字母，或者我们是否在末尾添加了一个额外的“A”，因为我们只有 Zs。我通过多个“REPLACE”增加字母 AY 的方式至少在优雅方面可能会有所改进，但它很快！
不过有一件事让我感到困惑。如果这是一个遗留问题，以前的开发人员是如何产生下一个价值的。想必他们是这样做的？
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句