Technical Article

Sequential Alpha Numeric String Incrementer

,

This function will increment the given alpha numeric string in sequential order up to 100 characters. (The length can be easily modified)

CREATE FUNCTION dbo.fn_IncrementAlphaNumericString
(
@string nvarchar(100)
)  
/*****************************************************************
** Name        : Sequential Alpha Numeric String Incrementer
**
** Description : This function will increment the given alpha 
**numeric string in sequential order up to 100 
**characters. (The length can be easily modified)
**            
** Written By: Mike Silva 2/14/05
**
** Parameters  : nvarchar string (100)
**
** Returns     : nvarchar string (100)                                         
**                  
*****************************************************************/RETURNS nvarchar(100) AS  
BEGIN 
DECLARE @NumericStringLen int, @LastAlphaPos int, @position int, 
@NewString nvarchar(100), @NumericString nvarchar(100),
@MaxNumValue nvarchar(100),  @AlphaString nvarchar(100),
@MaxStringValue nvarchar(100)

SET @position = 1

--Check to see if the given string is numeric.
IF ISNUMERIC(@string) <> 0
BEGIN
--The string is numeric so check to see if is at it's maximum numeric value.
  SET @MaxStringValue = REPLICATE('9',LEN(@string))
  IF @MaxStringValue = @string
  BEGIN
--The string is at it's maximum numeric value so add an alpha character.
SET @NewString = 'A' + REPLICATE('0',LEN(@string)-1)
  END
  ELSE
  BEGIN
--The number isn't at it's maximum numeric value so increment the number and pad with the zero's if necessary.
SET @NewString = RIGHT(REPLICATE('0',LEN(@string)) + CAST((CAST(@string as int) + 1) as nvarchar),LEN(@string))
  END
END
ELSE
BEGIN
--The string isn't numeric so find the position of the last alpha character
--by looping through the string character by character.
 WHILE @position <= LEN(@string)
 BEGIN
IF ISNUMERIC(SUBSTRING(@string,@position,1)) = 0
BEGIN
SET @LastAlphaPos = @position
END

   SET @position = @position + 1
 END

--Make sure the last alpha position is less than the length of the whole string.
 IF @LastAlphaPos < LEN(@string) 
 BEGIN
--Get the alpha portion of the string and change to uppercase characters so the acii range is correct.
SET @AlphaString = UPPER(SUBSTRING(@string,1,@LastAlphaPos))
--Get the numeric portion of the string.
SET @NumericString = SUBSTRING(@string,@LastAlphaPos +1,LEN(@string))
--Prepare a variable with the maximum numeric value to compare against the strings numeric value.
SET @MaxNumValue = REPLICATE('9',LEN(SUBSTRING(@string,@LastAlphaPos+1,LEN(@string))))
--Compare the numeric value of the string against the maximum numeric value .
IF  @MaxNumValue = @NumericString
BEGIN
--The numeric value of the string has reached the maximum value so check to see if the position
--of the last alpha character has reached the end of the string.
IF @LastAlphaPos < (LEN(@string) -1)
BEGIN
--The alpha characters haven't reached the end of the string so add another alpha character.
SET @NewString = @AlphaString + 'A' + REPLICATE('0',LEN(@NumericString)-1)
END
ELSE
BEGIN
--The alpha characters have reached the end of the string so check to see if the last alpha 
--character has reached it's maximum ascii value.
IF ASCII(SUBSTRING(@string,@LastAlphaPos,1)) = 90
BEGIN
--The last alpha character has reached it's maximum ascii value so find the position of the
--first alpha character that has reached it's maximum ascii value.
DECLARE @pos int
SET @pos = @LastAlphaPos
WHILE ASCII(SUBSTRING(@AlphaString,@pos,1)) = 90
BEGIN
SET @pos = @pos - 1
END
--Check to see if the position of the first alpha character that has reached it's maximum 
--ascii value is the first character in the whole string.
IF @pos > 1
BEGIN
--The first alpha character that has reached it's maximum ascii value isn't the first in the 
--string so add the first alpha characters to the beginning of the new string and increment 
--those that have reached their maximum value.
SET @NewString = LEFT(SUBSTRING(@AlphaString,1,@pos -1) + CHAR(ASCII(SUBSTRING(@AlphaString,@pos,1))+1) + REPLICATE('0',LEN(@string)),LEN(@string))
END
ELSE
BEGIN
--The first alpha character that has reached it's maximum ascii value is the first character in
--the string so increment it and pad the remainder of the string with zero's.
SET @NewString = LEFT(CHAR(ASCII(SUBSTRING(@string,1,1))+1) + REPLICATE('0',LEN(@string)),LEN(@string))
END
END
ELSE
BEGIN
--The last alpha character hasn't reached it's maximum ascii value so check to see if it is the first
--character in string.
IF @LastAlphaPos > 1
BEGIN
--The last alpha character isn't the first in the string so add the first alpha characters to the
--beginning of the new string and increment it and pad with zero's.
SET @NewString = LEFT(SUBSTRING(@string,1,@LastAlphaPos -1) + CHAR(ASCII(SUBSTRING(@string,@LastAlphaPos,1))+1) + REPLICATE('0',LEN(@string)),LEN(@string))
END
ELSE
BEGIN
--The last alpha character is the first in the string so increment it and pad the remainder of the string with zero's.
SET @NewString = LEFT(CHAR(ASCII(SUBSTRING(@string,1,1))+1) + REPLICATE('0',LEN(@string)),LEN(@string))
END
END
END
END
ELSE
BEGIN
--The numeric value of the string hasn't reached the maximum value so only increment the numeric portion of the string and pad with zero's if necessary.
SET @NewString = SUBSTRING(@string,1,@LastAlphaPos) + RIGHT(REPLICATE('0',LEN(@NumericString)) + CAST((CAST(SUBSTRING(@string,@LastAlphaPos +1,LEN(@string)) as int)+1) as nvarchar), LEN(@NumericString))
END
  END
END

RETURN @NewString
END

Rate

5 (1)

Share

Share

Rate

5 (1)