Lys
Reputation: 631
Generate sequence in alphanumeric in SQL Server
I am working a SQL Server stored procedure to generate a 10 digits sequence with the following patterns where [CustomerCode] is say 'ABC':
[CustomerCode]0000001
...
[CustomerCode]9999999
[CustomerCode]A000001
...
[CustomerCode]Z999999
[CustomerCode]ZA00001
...
[CustomerCode]ZZ99999
[CustomerCode]ZZA0001
...
[CustomerCode]ZZZZZZZ
So this stored procedure will take a string as input parameter, and return the next value e.g it takes 'ABCZ999999' and returns 'ABCZA00001'.
I have the following stored procedure and functions but they do not work fully and I can't seem to get it right.
Any help is appreciated.
-- 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
Upvotes: 2
Views: 420
Answers (1)
Jonathan Willcock
Reputation: 5245
Something like this should do the trick:
--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;
Obviously I have only done it as a straight query, but it is trivial to generate a stored procedure from it. The two key test cases are whether the number element is all 9s and the letter element is all Zs. If the number element is not all 9s then we get the next number part by cast to int adding 1 and casting back to varchar using REPLICATE to pad zeros. If the number element is all zeros, then the second key case comes in. Do we have a letter that we can increment, or do we add an extra 'A' at the end, because we only have Zs. The way I incremented the letters A-Y via multiple 'REPLACE' might be improved at least as far as elegance is concerned, but it is quick!
One thing puzzles me though. If this is a legacy issue, how were the previous developers generating the next value. Presumably they were doing so?
Upvotes: 1
Related Questions
- Creating Sequence in Sql Server
- Generate Sequence Code A-Z then 0-9 on SQL Server
- Alpha-numeric sequence in SQL Server
- Generate alphanumeric sequence with sql stored procedure
- SQL to generate next sequence in an alphanumeric id
- Generated number sequence
- SQL code to generate next sequence in a alphanumeric string
- Oracle stored procedure to generate sequence numbers?
- solution for generating a running sequence number by using stored procedure in sql server 2008
- generating sequence number