September 9, 2003 at 10:43 am
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 @CurrentSeriesLettervarchar(2)
,@MaxSeriesLetter INT
,@seriesLetter1 VARCHAR(1)
,@seriesLetter2 VARCHAR(1)
,@NewSeriesLetter VARCHAR(2)
SET @CurrentSeriesLetter = 'BC'
PRINT 'Current Series Letter: ' + @CurrentSeriesLetter
-- now get the next letter in the series.
SET @MaxSeriesLetter = ASCII('X')
IF LEN(@CurrentSeriesLetter) > 0 BEGIN
-- test for length. If the Revision is only one letter then it is easy to handel.
IF LEN(@CurrentSeriesLetter) > 0 AND LEN(@CurrentSeriesLetter) < 2 BEGIN
-- if the revision letter is a Z then we can not increment it using the following method
-- so set it.
IF (@CurrentSeriesLetter != 'Z') BEGIN
IF ASCII(LTRIM(RTRIM(@CurrentSeriesLetter))) <> @MaxSeriesLetter BEGIN
SET @NewSeriesLetter = CHAR(ASCII(@CurrentSeriesLetter)+1)
END
END
ELSE BEGIN
SET @NewSeriesLetter = 'X'
END
END
ELSE BEGIN
-- get the letters in the Revision
SET @seriesLetter1 = SUBSTRING(@CurrentSeriesLetter,0,LEN(@CurrentSeriesLetter) )
SET @seriesLetter2 = SUBSTRING(@CurrentSeriesLetter,2,LEN(@CurrentSeriesLetter) )
-- 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) <> @MaxSeriesLetter) BEGIN
SET @seriesLetter2 = CHAR(ASCII(@seriesLetter2)+1)
SELECT @NewSeriesLetter = @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 @NewSeriesLetter = @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 @NewSeriesLetter = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'X') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'A'
SELECT @NewSeriesLetter = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'Z'
SET @seriesLetter2 = 'X'
SELECT @NewSeriesLetter = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewSeriesLetter = 'The Revision XX is the last revision possible.'
END
END
END
END
-- for debug when run in Query Analyzer
PRINT 'The Next in the Series is ' + @NewSeriesLetter
----------------------------------------------------------------------
September 12, 2003 at 8:00 am
This was removed by the editor as SPAM
September 12, 2003 at 11:37 am
Suggestion:
Click on Community, click on Contribution Center and contribute your code. This way it will be placed in the Script list (go to Home and scroll down for a list of scripts or go to Resources > Scripts).
-SQLBill
September 13, 2003 at 10:47 am
Good idea, please post it.
I'd prbably attack it by getting the ASCII code and incrementing that, rolling over on Z, but that's me.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 15, 2003 at 11:35 am
quote:
Good idea, please post it.I'd prbably attack it by getting the ASCII code and incrementing that, rolling over on Z, but that's me.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Thanks for the reply's guy's.
I just wanted to post this incase anyone was wondering how to increment a letter. I have tried to post scripts to the script library but I get errors when I submit. Not sure why.
Well thanks again
William O'Malley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply