USE [Aadhya_Test]
GO
/****** Object: UserDefinedFunction [dbo].[fn_IncrementAlphaNumericString] Script Date: 04/18/2013 11:37:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_IncrementAlphaNumericString]
(
@InvID nvarchar(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(@InvID) <> 0
BEGIN
--The string is numeric so check to see if is at it's maximum numeric value.
SET @MaxStringValue = REPLICATE('9',LEN(@InvID))
IF @MaxStringValue = @InvID
BEGIN
--The string is at it's maximum numeric value so add an alpha character.
SET @NewString = 'A' + REPLICATE('0',LEN(@InvID)-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(@InvID)) + CAST((CAST(@InvID as int) + 1) as nvarchar),LEN(@InvID))
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(@InvID)
BEGIN
IF ISNUMERIC(SUBSTRING(@InvID,@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(@InvID)
BEGIN
--Get the alpha portion of the string and change to uppercase characters so the acii range is correct.
SET @AlphaString = UPPER(SUBSTRING(@InvID,1,@LastAlphaPos))
--Get the numeric portion of the string.
SET @NumericString = SUBSTRING(@InvID,@LastAlphaPos +1,LEN(@InvID))
--Prepare a variable with the maximum numeric value to compare against the strings numeric value.
SET @MaxNumValue = REPLICATE('9',LEN(SUBSTRING(@InvID,@LastAlphaPos+1,LEN(@InvID))))
--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(@InvID) -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(@InvID,@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(@InvID)),LEN(@InvID))
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(@InvID,1,1))+1) + REPLICATE('0',LEN(@InvID)),LEN(@InvID))
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(@InvID,1,@LastAlphaPos -1) + CHAR(ASCII(SUBSTRING(@InvID,@LastAlphaPos,1))+1) + REPLICATE('0',LEN(@InvID)),LEN(@InvID))
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(@InvID,1,1))+1) + REPLICATE('0',LEN(@InvID)),LEN(@InvID))
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(@InvID,1,@LastAlphaPos) + RIGHT(REPLICATE('0',LEN(@NumericString)) + CAST((CAST(SUBSTRING(@InvID,@LastAlphaPos +1,LEN(@InvID)) as int)+1) as nvarchar), LEN(@NumericString))
END
END
END
RETURN @NewString
END