Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Auto Increment Alphabet Expand / Collapse
Author
Message
Posted Thursday, May 27, 2010 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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


AA
AB
AC
...
AZ
BA
BB
BC
...
BZ
CA
CB
CC
...
CZ
....
....
ZA
ZB
...
ZZ
AAA
...........




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 B


On 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)
BEGIN
SET @FP2='A'
SET @FP1=CHAR(ASCII(@FP1)+1)
IF(ASCII(@FP1)>90)
BEGIN
SET @FP1='A'
SET @FP='A'+@FP1+@FP2
END
ELSE
BEGIN
SET @FP=@FP1+@FP2
END
END
ELSE IF(ASCII(@FP2)<90)
BEGIN
SET @FP2=CHAR(ASCII(@FP2)+1)
SET @FP=@FP1+@FP2
END


SELECT @FP, @FP1, ASCII(@FP1), @FP2, ASCII(@FP2)



I hope someone helps.


Ankit Mathur
Post #929207
Posted Thursday, May 27, 2010 2:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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...


Wayne
Microsoft Certified Master: SQL Server 2008
If 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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 TALLY
UNION ALL
SELECT CHAR(64 + C2) + CHAR(64 + C1) FROM DEUX
UNION ALL
SELECT CHAR(64 + C3)+ CHAR(64 + C2) + CHAR(64 + C1) FROM TROIS
UNION ALL
SELECT 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 elapsed
1 391 387 672 376
2 375 389 671 693
3 390 402 672 673
4 391 388 703 690
5 391 386 671 672
Result 387.6 390.4 677.8 620.8
Post #929475
Posted Friday, May 28, 2010 12:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 mine

Modified WayneS' Code:

;WITH 
/*
--Am commenting this section of the code which is taking that extra time

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),*/
;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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #929950
Posted Friday, May 28, 2010 8:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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!


Wayne
Microsoft Certified Master: SQL Server 2008
If 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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?


Wayne
Microsoft Certified Master: SQL Server 2008
If 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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #930006
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse