Auto Increment Alphabet

Question

Post reply

Auto Increment Alphabet

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ankit Mathur-481681 SSCrazy Points: 2074 More actions · Answer 1

Hi Guys,

I'm so sorry I couldn't pitch in before.

Wayne - Well, you did say you only needed it to get to AAAA. ZZZZ is much higher! 😉

@Wayne: Desires are insatiable 😛

Jeff - The lack of feedback on this post has me worried that the OP is actually using the alphabetic increment in the unfiltered mode. Please save your company a lawsuit and don't do it.

@jeff: Yep .. you are right Jeff. Frankly, I didn't thought all that far. Your posts are enlightening.

As for lack on feedback on your attempts. I hope it doesn't put you both off but pardon my knowledge of Base 26 numbers. Its a new concept for me as I haven't used or, seen it being used anywhere so far & all this while I was & am actually trying to gain knowledge of Base 26 numbering system so that I can actually understand & implement your code.

Ankit

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Thanks for the feedback, Ankit...

There's a fair bit of complexity to using a base 26 "numbering" system like this even if you don't take into account all the swear words that can be realized. I still recommend avoiding it if you can.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajesh_somvanshi SSC Enthusiast Points: 132 More actions · Answer 3

rajesh_somvanshi

SSC Enthusiast

Points: 132

October 2, 2013 at 12:42 am

#1655124

Very Nice...thank you so much.

webridges SSC Journeyman Points: 83 More actions · Answer 4

I needed something to increment alphabetically for labeling revisions and stumbled across this thread. I tried several of the "mathematical" ways at attempting BASE26, but I could never get it to work since I needed A to be 1 and a leading blank/null to be 0 so that Z would roll to AA, etc. So I just went at it from another angle of simply dealing with the characters themselves.

In case anyone else has the need, this is what I ended up with: (Could be cleaned up, but this leaves it somewhat readable.)

DECLARE @revision VARCHAR(20) = 'Z'; -- String to increment

DECLARE @Characters VARCHAR(50) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; -- Characters to use

DECLARE @CarryOver INT = 0;

DECLARE @Interation INT = 0;

DECLARE @CharIndex INT;

WHILE @CarryOver > 0

OR @Interation = 0

BEGIN

IF @Interation >= LEN(@Revision)

BEGIN

SET @revision = SUBSTRING(@Characters, @CarryOver, 1) + @revision;

SET @CarryOver = @CarryOver / LEN(@Characters);

END;

ELSE

BEGIN

SET @CharIndex = CHARINDEX(SUBSTRING(REVERSE(@Revision), @Interation + 1, 1), @Characters) + 1;

SET @revision = STUFF(@Revision, LEN(@Revision) - @Interation, 1, SUBSTRING(@Characters, @CharIndex % LEN(@Characters), 1));

SET @CarryOver = @CharIndex / LEN(@Characters);

END;

SET @Interation = @Interation + 1;

END;

SELECT @revision;