InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 10:54:51 GMT20http://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxThanks 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.Sat, 05 Jun 2010 07:21:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxHi Guys,I'm so sorry I couldn't pitch in before. [quote]Wayne - Well, you did say you only needed it to get to AAAA. ZZZZ is much higher! ;-)[/quote]@Wayne: Desires are insatiable :-P[quote]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. [/quote]@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. [u]I hope it doesn't put you both off[/u] 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.AnkitWed, 02 Jun 2010 23:28:42 GMTAnkit Mathur-481681http://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxGoodness me, i lost track of this thread and now i saw that this alpha-numbering is a huge swear-word dump-yard.. hmmm,as u said Jeff, it will invite lawsuits.. lets wait for the OP to post back his thoughts...Tue, 01 Jun 2010 10:26:00 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxThe 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.Tue, 01 Jun 2010 09:12:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxEven though I still don't like this kind of "numbering", let's try things in a slightly different manner. If we remove all the vowels from the alphabet, it's a wee bit more difficult to spell swear words.First, build the following scalar function. It's got to be scalar because we're still going to use it in a computed column. It can't be persisted, either, because it ends up being non-deterministic in this case and I haven't tried to figure out how to make it so because it actually runs faster than the previous example...[code="sql"] CREATE FUNCTION dbo.Base21--===== This function accepts an integer and returns mostly harmless -- "numbering" using letters with all vowels removed. -- Jeff Moden (@Integer INT)RETURNS VARCHAR(8) AS BEGINDECLARE @Return VARCHAR(8);WITHcteAllowed AS( SELECT 'BCDFGHJKLMNPQRSTVWXYZ' AS Letters) SELECT @Return = SUBSTRING(Letters, @Integer/1801088541%21+1, 1) --21^7 + SUBSTRING(Letters, @Integer/85766121%21+1, 1) --21^6 + SUBSTRING(Letters, @Integer/4084101%21+1, 1) --21^5 + SUBSTRING(Letters, @Integer/194481%21+1, 1) --21^4 + SUBSTRING(Letters, @Integer/9261%21+1, 1) --21^3 + SUBSTRING(Letters, @Integer/441%21+1, 1) --21^2 + SUBSTRING(Letters, @Integer/21%21+1, 1) --21^1 + SUBSTRING(Letters, @Integer%21+1, 1) --21^0 FROM cteAllowed; RETURN @Return ENDGO[/code]Now we can do this without all the swear words... [code="sql"]--===== Create a table with a real auto-incrementing column and -- a persisted formula to convert it to Base 21 (no vowels) automatically. -- The auto-incrementing column is "Zero Based" to keep things simple. CREATE TABLE #MyHead ( MyHeadID INT IDENTITY(0,1) NOT NULL, Base21ID AS dbo.Base21(MyHeadID) , SomeString VARCHAR(36) NOT NULL )--===== Add a million+1 rows of "something" to the table -- just to prove it works. INSERT INTO #MyHead (SomeString) SELECT TOP 1000000 NEWID() AS SomeString FROM Master.sys.All_Columns ac1 CROSS JOIN Master.sys.All_Columns ac2--===== Show what's in the table SELECT * FROM #MyHead[/code]Sat, 29 May 2010 00:26:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspx@Ankit (or anyone else who thinks this type of "numbering" is a good idea),Let me tell you why BASE 26 "numbering" like this is SO bad that it'll likely get your company sued... try the following code with your choice of classic "3 and 4 letter" swear words and see what you get...[code="sql"]SELECT * FROM #MyHead WHERE Base26ID LIKE '%CRAP%'[/code]Every soccer mom and little old lady in the world will be out to get you and your company if these numbers ever are exposed publicly.;-)Fri, 28 May 2010 23:28:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxAlright... here you go. First, we'll demo a way to accomplish the task and then I'll show in the post following this one why this type of numbering should NEVER be done.The way the following code works is that it actually uses an IDENTITY column to provide a true auto-incrementing capability instead of having to do any manual sequencing. That's important because instead of using a bunch of self joins to an "external number generator" or sequencer of any kind, we can use a Computed Column, instead.[font="Arial Black"]I still think this type of "numbering" is a huge mistake (see the next post down for why).[/font][code="sql"]--===== Create a table with a real auto-incrementing column and -- a persisted formula to convert it to Base 26 automatically. -- The auto-incrementing column is "Zero Based" to keep things simple. -- Max value = 26^7-1 = 8,031,810,175 > Largest INT CREATE TABLE #MyHead ( MyHeadID INT IDENTITY(0,1) NOT NULL, Base26ID AS CHAR(MyHeadID/308915776%26+65) --26^6 + CHAR(MyHeadID/11881376%26+65) --26^5 + CHAR(MyHeadID/456976%26+65) --26^4 + CHAR(MyHeadID/17576%26+65) --26^3 + CHAR(MyHeadID/676%26+65) --26^2 + CHAR(MyHeadID/26%26+65) --26^1 + CHAR(MyHeadID%26+65) --26^0 PERSISTED NOT NULL, SomeString VARCHAR(36) NOT NULL )--===== Add a million+1 rows of "something" to the table -- just to prove it works. INSERT INTO #MyHead (SomeString) SELECT TOP 1000001 NEWID() AS SomeString FROM Master.sys.All_Columns ac1 CROSS JOIN Master.sys.All_Columns ac2--===== Show what's in the table SELECT * FROM #MyHead[/code]Fri, 28 May 2010 22:54:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxThis isn't right. First, using base 26 numbering isn't ever the right thing to do IMHO but if you're going to do it, let's do it so it's not a "manual" sequence or externally generated. Using base 26 numbering directly will only cause problems because it's a manual sequence fraught with all the problems of such manual sequences. There are some other MAJOR problems with such numbering that we'll do a little demo for. I'll be back in about a half hour with a method that will do the base 26 numbering and a demo for why it should NEVER be used.Lordy, I hate it when people levy these types of requirements on perfectly good data.:crying::hehe:Fri, 28 May 2010 22:05:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspx[quote][b]ColdCoffee (5/28/2010)[/b][hr]A slight modification on WayneS' code is producing exact simliar test timings as mine...@Wayne Shef, mate, am not as experienced or as agile or as smart as u are in coding; please dont mistake me for editing your code, just that i made small correction so that it runs even faster than what i did. If you feel i am doing something wrong here, i apologize! :-)[/quote]:blush:I don't mind... I like seeing other ways that might be better. I knew that reducing the virtual tally table would probably make it faster... it's just that for those virtual tally tables I have this code snippet all set up to use, and I didn't bother changing it.One thing though... the code I put up returns the next value for a submitted value. Yours returns the entire set. How do you go about getting the next value? Until both code return the same thing, it's useless to compare. Do you have something that returns the next value for the specified value?Fri, 28 May 2010 20:25:25 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspx[quote][b]Ankit Mathur-481681 (5/27/2010)[/b][hr]But struggling to make it a dynamic option to go to AAA & once it reaches ZZZ to AAAA.[/quote][quote]Thanks Wayne,Your solution fits the bill till ZZZZ. I think for the time being does solve my problem.But as you suggested a possible better solution may be forthcoming. I'm surely waiting along with you for the same.Thanks again.Ankit :-D[/quote]Well, you did say you only needed it to get to AAAA. ZZZZ is much higher! ;-)Fri, 28 May 2010 20:17:08 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspx[quote][b]ColdCoffee (5/28/2010)[/b][hr]@Wayne Shef, mate, am not as experienced or as agile or as smart as u are in coding; please dont mistake me for editing your code, just that i made small correction so that it runs even faster than what i did. If you feel i am doing something wrong here, i apologize! :-)[/quote]Heh... everyone loves a food fight over performance. However, the "doing something wrong here" may be how you're timing things... tests that return output to the screen are mostly invalid because the screen is the "great equalizer" when it comes to duration. ;-)Fri, 28 May 2010 15:29:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxA slight modification on WayneS' code is producing exact simliar test timings as mineModified WayneS' Code:[code="sql"];WITH /* --Am commenting this section of the code which is taking that extra timeTens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),*/;WITH TALLY AS( SELECT 1 N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26),CTE1 (A) AS (SELECT CHAR(N+64) FROM Tally WHERE N between 1 and 26),CTE2 (B) AS (SELECT c1.A + c2.A FROM CTE1 c1 CROSS JOIN CTE1 c2),CTE3 (C) AS (SELECT c1.A + c2.A + c3.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3),CTE4 (D) AS (SELECT c1.A + c2.A + c3.A + c4.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3 CROSS JOIN CTE1 c4),CTE AS (SELECT A, RN = 1 FROM CTE1 UNION ALL SELECT B, RN = 2 FROM CTE2 UNION ALL SELECT C, RN = 3 FROM CTE3 UNION ALL SELECT D, RN = 4 FROM CTE4 )SELECT A FROM CTE [/code]@Wayne Shef, mate, am not as experienced or as agile or as smart as u are in coding; please dont mistake me for editing your code, just that i made small correction so that it runs even faster than what i did. If you feel i am doing something wrong here, i apologize! :-)Fri, 28 May 2010 00:41:17 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxTest results on 5 runs between mine and WayneS'[code="plain"] Me wayne cpu elapsed cpu elapsed1 391 387 672 3762 375 389 671 6933 390 402 672 6734 391 388 703 6905 391 386 671 672Result 387.6 390.4 677.8 620.8[/code]Fri, 28 May 2010 00:36:22 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxHere's my solution.[code="sql"];WITH TALLY AS( SELECT 1 N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26),DEUX AS( SELECT T1.N C1 , T2.N C2 FROM TALLY T1 CROSS JOIN TALLY T2),TROIS AS( SELECT T1. C1 , T1.C2 C2, T2.N C3 FROM DEUX T1 CROSS JOIN TALLY T2),QUATRE AS( SELECT T1. C1 , T1.C2 C2, T1.C3 C3 , T2.N C4 FROM TROIS T1 CROSS JOIN TALLY T2)SELECT CHAR(64 + N) ALPHA FROM TALLYUNION ALLSELECT CHAR(64 + C2) + CHAR(64 + C1) FROM DEUXUNION ALLSELECT CHAR(64 + C3)+ CHAR(64 + C2) + CHAR(64 + C1) FROM TROISUNION ALLSELECT CHAR(64 + C4)+ CHAR(64 + C3)+ CHAR(64 + C2) + CHAR(64 + C1) FROM QUATRE[/code]~Edit : Removed one extra [i]select[/i]Fri, 28 May 2010 00:24:03 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxThanks Wayne,Your solution fits the bill till ZZZZ. I think for the time being does solve my problem.But as you suggested a possible better solution may be forthcoming. I'm surely waiting along with you for the same.Thanks again.Ankit :-DThu, 27 May 2010 23:20:42 GMTAnkit Mathur-481681http://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxThis little bit of code makes a dynamic table of the possible strings, then gets the next one from what is passed in.[code="sql"]declare @test varchar(4)set @test = 'AAZ'-- See Jeff Modem's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.;WITH Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),CTE1 (A) AS (SELECT CHAR(N+64) FROM Tally WHERE N between 1 and 26),CTE2 (B) AS (SELECT c1.A + c2.A FROM CTE1 c1 CROSS JOIN CTE1 c2),CTE3 (C) AS (SELECT c1.A + c2.A + c3.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3),CTE4 (D) AS (SELECT c1.A + c2.A + c3.A + c4.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3 CROSS JOIN CTE1 c4),CTE AS (SELECT A, RN = 1 FROM CTE1 UNION ALL SELECT B, RN = 2 FROM CTE2 UNION ALL SELECT C, RN = 3 FROM CTE3 UNION ALL SELECT D, RN = 4 FROM CTE4)SELECT TOP 1 A FROM CTE WHERE A > @test and RN >= LEN(@test)[/code]I'm sure Paul will come along with a nice CROSS APPLY to do the job...Thu, 27 May 2010 14:24:20 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic929207-1292-1.aspxHi All,I need to generate a query that'll prefix my form series like the below AAABAC...AZBABBBC...BZCACBCC...CZ........ZAZB...ZZAAA...........Basically, I need to increment my form prefix with the next alphabet and if Z had already been reached to increment the first letter i.e. A to BOn my part I've developed a logic for 2 character series. But struggling to make it a dynamic option to go to AAA & once it reaches ZZZ to AAAA.Here's my attempt[code="plain"]DECLARE @FP VARCHAR(3)DECLARE @FP1 VARCHAR(1)DECLARE @FP2 VARCHAR(1)SET @FP = 'AAA'SET @FP1 = SUBSTRING(@FP,LEN(@FP)-1,1)SET @FP2 = SUBSTRING(@FP,LEN(@FP),1)SELECT @FP, @FP1, ASCII(@FP1), @FP2, ASCII(@FP2)IF(ASCII(@FP2)=90)BEGINSET @FP2='A' SET @FP1=CHAR(ASCII(@FP1)+1)IF(ASCII(@FP1)>90)BEGINSET @FP1='A'SET @FP='A'+@FP1+@FP2ENDELSEBEGINSET @FP=@FP1+@FP2ENDENDELSE IF(ASCII(@FP2)<90)BEGINSET @FP2=CHAR(ASCII(@FP2)+1)SET @FP=@FP1+@FP2ENDSELECT @FP, @FP1, ASCII(@FP1), @FP2, ASCII(@FP2)[/code]I hope someone helps. Ankit MathurThu, 27 May 2010 12:20:29 GMTAnkit Mathur-481681