Have you ever needed to Increment a revision letter?

[womalley]

Hi,
I just wanted to post this for an Example.
I ran into a situation where I needed to increment a Revision. The revision could be up to two letters in length. The following code works well. Please let me know if there are any questions.

declare @r varchar(2)
declare @MaxRevLetter INT
declare @seriesLetter1 VARCHAR(1)
declare @seriesLetter2 VARCHAR(1)
declare @NewRevision VARCHAR(2)

-- SET Revision Letter(s) to Check
SET @r = 'CX'
SET @MaxRevLetter = ASCII('X')
-- test for length. If the Revision is only one letter then it is easy to handel.
IF LEN(@r) > 0 AND LEN(@r) < 2 BEGIN
-- if the revision letter is a Z then we can not increment it using the following method
-- so set it.
IF (@r != 'Z') BEGIN
IF ASCII(LTRIM(RTRIM(@r))) <> @MaxRevLetter BEGIN
SET @NewRevision = CHAR(ASCII(@r)+1)
END
END
ELSE BEGIN
SET @NewRevision = 'X'
END
END
ELSE BEGIN
-- get the letters in the Revision
SET @seriesLetter1 = SUBSTRING(@r,0,LEN(@r) )
SET @seriesLetter2 = SUBSTRING(@r,2,LEN(@r) )
-- because we know that if there is a Z the letter must be set and can not be
-- incremented. We need to test for a Z.
IF (@seriesLetter1 + @seriesLetter2 != 'ZZ' AND
@seriesLetter1 + @seriesLetter2 != 'ZX' AND
@seriesLetter1 + @seriesLetter2 != 'XZ' AND
@seriesLetter1 + @seriesLetter2 != 'XX') BEGIN
-- Ok if the first letter is NOT an X and the second letter is not an X
-- increment the second letter so AB would be AC
IF (ASCII(@seriesLetter2) <> @MaxRevLetter) BEGIN
SET @seriesLetter2 = CHAR(ASCII(@seriesLetter2)+1)
SELECT @NewRevision = @seriesLetter1 + @seriesLetter2
END
ELSE BEGIN
-- ok the second letter was an X Ex: AX so we need to increment the first letter
-- and set the second letter back. AX would be BA
SET @seriesLetter1 = CHAR(ASCII(@seriesLetter1)+1)
SET @seriesLetter2 = 'A'
SELECT @NewRevision = @seriesLetter1 + @seriesLetter2
END
END
ELSE BEGIN
-- ok there is some combo of X and Z in the string. so set the value
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'X'
SELECT @NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'X') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'A'
SELECT @NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'Z'
SET @seriesLetter2 = 'X'
SELECT @NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = @seriesLetter1 = 'The Revision XX is the last revision possable.'
END
END
END

-- now output the New Revision Letter(s)
SELECT @NewRevision


enjoy
Will

[womalley]

The following line is not correct:
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = @seriesLetter1 = 'The Revision XX is the last revision possable.'
END

please change it to
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = 'The Revision XX is the last revision possable.'
END

Will