May 27, 2010 at 12:20 pm
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
May 27, 2010 at 2:24 pm
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-2 varchar(4)
set @test-2 = '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-2 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
Author - SQL Server T-SQL Recipes
May 27, 2010 at 11:20 pm
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 😀
May 28, 2010 at 12:24 am
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.NC1 , 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
May 28, 2010 at 12:36 am
Test results on 5 runs between mine and WayneS'
Mewayne
cpuelapsedcpu elapsed
1391387672376
2375389671693
3390402672673
4391388703690
5391386671672
Result387.6390.4677.8620.8
May 28, 2010 at 12:41 am
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! 🙂
May 28, 2010 at 3:29 pm
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
Change is inevitable... Change for the better is not.
May 28, 2010 at 8:17 pm
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
Author - SQL Server T-SQL Recipes
May 28, 2010 at 8:25 pm
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! 🙂
: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?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 28, 2010 at 10:05 pm
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.:crying::hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2010 at 10:54 pm
Alright... 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]
--===== 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
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2010 at 11:28 pm
@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...
SELECT * FROM #MyHead WHERE Base26ID LIKE '%CRAP%'
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.;-)
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2010 at 12:26 am
Even 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...
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
BEGIN
DECLARE @Return VARCHAR(8)
;
WITH
cteAllowed 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
END
GO
Now we can do this without all the swear words...
--===== 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
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2010 at 9:12 am
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 Moden
Change is inevitable... Change for the better is not.
June 1, 2010 at 10:26 am
Goodness 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...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply