Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Auto Increment Alphabet Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, May 27, 2010 12:20 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, April 16, 2014 12:08 PM Points: 167, Visits: 325
 Hi 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`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)`I hope someone helps. Ankit Mathur
Post #929207
 Posted Thursday, May 27, 2010 2:24 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 12:59 AM Points: 6,544, Visits: 8,761
 This little bit of code makes a dynamic table of the possible strings, then gets the next one from what is passed in.`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)`I'm sure Paul will come along with a nice CROSS APPLY to do the job... WayneMicrosoft Certified Master: SQL Server 2008If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #929297
 Posted Thursday, May 27, 2010 11:20 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, April 16, 2014 12:08 PM Points: 167, Visits: 325
 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
Post #929460
 Posted Friday, May 28, 2010 12:24 AM
 SSCrazy Group: General Forum Members Last Login: Monday, April 07, 2014 3:41 PM Points: 2,259, Visits: 5,395
 Here's my solution.`;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`~Edit : Removed one extra select
Post #929471
 Posted Friday, May 28, 2010 12:36 AM
 SSCrazy Group: General Forum Members Last Login: Monday, April 07, 2014 3:41 PM Points: 2,259, Visits: 5,395
 Test results on 5 runs between mine and WayneS'` 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`
Post #929475
 Posted Friday, May 28, 2010 12:41 AM
 SSCrazy Group: General Forum Members Last Login: Monday, April 07, 2014 3:41 PM Points: 2,259, Visits: 5,395
 A slight modification on WayneS' code is producing exact simliar test timings as mineModified WayneS' Code:`;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 `@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!
Post #929478
 Posted Friday, May 28, 2010 3:29 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 7:25 AM Points: 35,959, Visits: 30,253
 ColdCoffee (5/28/2010)@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! 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. --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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #929950
 Posted Friday, May 28, 2010 8:17 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 12:59 AM Points: 6,544, Visits: 8,761
 Ankit Mathur-481681 (5/27/2010)But struggling to make it a dynamic option to go to AAA & once it reaches ZZZ to AAAA.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 Well, you did say you only needed it to get to AAAA. ZZZZ is much higher! WayneMicrosoft Certified Master: SQL Server 2008If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #929997
 Posted Friday, May 28, 2010 8:25 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 12:59 AM Points: 6,544, Visits: 8,761
 ColdCoffee (5/28/2010)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! 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? WayneMicrosoft Certified Master: SQL Server 2008If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #929998
 Posted Friday, May 28, 2010 10:05 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 7:25 AM Points: 35,959, Visits: 30,253
 This 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. --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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #930006

 Permissions