April 26, 2010 at 10:06 am
I am attempting to convert the row number to an alpha character:
EX 1 = A, 2 = B,...27 = AA, 28= BB,... 53 = AAA, 54 = BBB, ...
I was able to get part of it to work. Below is the code I have so far that works till 25 (Y), but with 26 it returns '@'... I added comments above some lines in the code. I tried to use CAST and CONVERT without luck...
CODE:
IF OBJECT_ID(N'AlphaCounter') IS NOT NULL
DROP Function AlphaCounter
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Paul Morris
-- Create date: 25 April 2010
-- Description:Function to convert rownumber to alpha.
-- http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
-- ORIGINAL CODE:
-- create function CustomerNumber (@id int)
-- returns char(5)
-- as
-- begin
-- return char(@id / power(26,3) % 26 + 65) +
-- char(@id / power(26,2) % 26 + 65) +
-- char(@id / 26 % 26 + 65) +
-- char(@id % 26 + 65)
-- end
-- =============================================
-- =============================================
CREATE FUNCTION [dbo].[AlphaCounter] (@alphacount int)
RETURNS char
AS
BEGIN
return char
(
-- breaks: zero returned = '@'
-- char(@alphacount / POWER(26,2) % 26 + 64) +
-- breaks: with 'char'=converting varchar value to data type int error. With 'char' removed: returns wrong value
-- char(@alphacount / 26 % 26 + 64) +
-- works without 'char'. adding 'char' returns converting varchar value to data type int error
(@alphacount % 26 + 64 )
)
END
GO
-- SELECT dbo.alphacounter (1)-- returns A
-- SELECT dbo.AlphaCounter (26)-- supposed to return Z
-- SELECT dbo.AlphaCounter (27) -- supposed to return AA
-- SELECT (30 / 26 % 26 + 64)-- returns 65
-- SELECT dbo.AlphaCounter (28) -- supposed to return BB
-- SELECT dbo.AlphaCounter (53) -- supposed to return AAA
Any guidance would be appreciated.... Thanks!
April 26, 2010 at 10:31 am
Using my Tally table to provide the "rownumber" the following code is what I came up with, hope it helps:
select top (26 * 3)
N,
(((N - 1)/ 26) + 1),
(((N - 1) % 26) + 1),
replicate(char(64 + (((N - 1) % 26) + 1)),(((N - 1)/ 26) + 1))
from
dbo.Tally
April 26, 2010 at 10:53 am
thanks Lynn... looks promising based on some quick testing. It does return what I was expecting. Let me incorporate it with what I have and test...
Thanks again!
April 26, 2010 at 11:20 am
Please post your final code, as there may be a possible improvement based on what Ihave seen already.
April 26, 2010 at 11:49 am
Paul Morris-1011726 (4/26/2010)
I am attempting to convert the row number to an alpha character:EX 1 = A, 2 = B,...27 = AA, 28= BB,... 53 = AAA, 54 = BBB, ...
I was able to get part of it to work. Below is the code I have so far that works till 25 (Y), but with 26 it returns '@'... I added comments above some lines in the code. I tried to use CAST and CONVERT without luck...
CODE:
IF OBJECT_ID(N'AlphaCounter') IS NOT NULL
DROP Function AlphaCounter
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Paul Morris
-- Create date: 25 April 2010
-- Description:Function to convert rownumber to alpha.
-- http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
-- ORIGINAL CODE:
-- create function CustomerNumber (@id int)
-- returns char(5)
-- as
-- begin
-- return char(@id / power(26,3) % 26 + 65) +
-- char(@id / power(26,2) % 26 + 65) +
-- char(@id / 26 % 26 + 65) +
-- char(@id % 26 + 65)
-- end
-- =============================================
-- =============================================
CREATE FUNCTION [dbo].[AlphaCounter] (@alphacount int)
RETURNS char
AS
BEGIN
return char
(
-- breaks: zero returned = '@'
-- char(@alphacount / POWER(26,2) % 26 + 64) +
-- breaks: with 'char'=converting varchar value to data type int error. With 'char' removed: returns wrong value
-- char(@alphacount / 26 % 26 + 64) +
-- works without 'char'. adding 'char' returns converting varchar value to data type int error
(@alphacount % 26 + 64 )
)
END
GO
-- SELECT dbo.alphacounter (1)-- returns A
-- SELECT dbo.AlphaCounter (26)-- supposed to return Z
-- SELECT dbo.AlphaCounter (27) -- supposed to return AA
-- SELECT (30 / 26 % 26 + 64)-- returns 65
-- SELECT dbo.AlphaCounter (28) -- supposed to return BB
-- SELECT dbo.AlphaCounter (53) -- supposed to return AAA
Any guidance would be appreciated.... Thanks!
If 27 = AA, then why would 28 = BB??? Shouldn't 28 = AB?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 26, 2010 at 12:07 pm
In theory yes but in this particular case they want A, B, C...,AA, BB, CC..., AAA, BBB, CCC....
If interested I could spell out why but figured most folks are not interested...
April 26, 2010 at 1:58 pm
Lynn Pettis (4/26/2010)
Please post your final code, as there may be a possible improvement based on what Ihave seen already.
OK been attempting to get this working in a function but I do not seem to be getting it.... I do need this as a function. It will be used in several reports.
Here is where I am .... with an error "The char function requires 1 argument":
IF OBJECT_ID(N'AlphaCounter') IS NOT NULL
DROP Function AlphaCounter
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[AlphaCounter] (@alphacount int)
RETURNS char
AS
BEGIN
RETURN char
(
@alphacount,
(((@alphacount - 1)/ 26) + 1),
(((@alphacount - 1) % 26) + 1),
replicate(char(64 + (((@alphacount - 1) % 26) + 1)),(((@alphacount - 1)/ 26) + 1))
)
END
GO
-- SELECT dbo.alphacounter (1) -- returns A
-- SELECT dbo.AlphaCounter (27) -- supposed to return AA
-- SELECT dbo.AlphaCounter (53) -- supposed to return AAA
I tested what you sent with this and it works fine:
CREATE TABLE #tally (alphacount int)
INSERT INTO #tally (alphacount)
SELECT 30 UNION ALL
SELECT 53 UNION ALL
SELECT 54 UNION ALL
SELECT 27 UNION ALL
SELECT 26 UNION ALL
SELECT 1 UNION ALL
SELECT 60 UNION ALL
SELECT 70
SELECT TOP (26 * 3)
alphacount,
(((alphacount - 1)/ 26) + 1),
(((alphacount - 1) % 26) + 1),
replicate(char(64 + (((alphacount - 1) % 26) + 1)),(((alphacount - 1)/ 26) + 1))
FROM #Tally
April 26, 2010 at 2:08 pm
Something like this maybe?
CREATE FUNCTION [dbo].[AlphaCounter] (
@alphacount int
)
RETURNS table
AS
RETURN select replicate(char(64 + (((@alphacount - 1) % 26) + 1)),(((@alphacount - 1)/ 26) + 1)) as AlphaRow
select
N,
AlphaRow
from
dbo.Tally
cross apply dbo.AlphaCounter(N);
April 26, 2010 at 3:06 pm
By the way, if the row_number is a part of the query, you really don't need a function. Just use this:
replicate(char(64 + (((@alphacount - 1) % 26) + 1)),(((@alphacount - 1)/ 26) + 1)) as AlphaRow
in your select list of your query replacing the @alphacount variable with the column you are converting.
April 27, 2010 at 7:31 am
Thanks Lynn... used the last suggestion and just plugged into query with out doing a function.
Here is the snippet of the query:
replicate(char(64 + ((((ROW_NUMBER() OVER(ORDER BY vle.computename)) - 1) % 26) + 1)),((((ROW_NUMBER() OVER(ORDER BY vle.computename)) - 1)/ 26) + 1)) as AlphaRow
Works... tested against production data that returns A - QQ.... Looking for a larger one now.
Thanks again!
April 27, 2010 at 8:26 am
When I first read this I thought the same as Jeff and managed to find a very interesting thread with some examples of how to do it (1=A, 26=Z, 27=AA, 53=BA Etc)
It can be found here:
I quite liked Plamen Ratchev's Tally Method:
;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
Chars (ch) AS (SELECT CHAR(n)
FROM Nums
WHERE n BETWEEN 65 AND 90)
SELECT DISTINCT n, LEFT(I.ch + II.ch + III.ch, n) AS sequence
FROM Chars AS I
CROSS JOIN Chars AS II
CROSS JOIN Chars AS III
CROSS JOIN Nums
WHERE n <= 3
ORDER BY n, sequence;
Edit: Fixed URL!
April 28, 2010 at 3:47 am
Dohsan (4/27/2010)
I quite liked Plamen Ratchev's Tally Method...
You are right - this is a much more interesting problem! I haven't checked the link you posted yet, because I wanted to have a go myself first:
SELECT TOP (18278)
N.n,
sequence =
RIGHT
(
CHAR(65 + ((((N.n - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + (((N.n - 1) / 26 - 1) % 26)) +
CHAR(65 + ((N.n - 1) % 26))
, LOG(N.n - N.n / 26) / LOG(26) + 1
)
FROM dbo.Numbers(18278) N
ORDER BY N.n ASC;
The code uses my regular in-line numbers table generator:
ALTER FUNCTION dbo.Numbers
(
@Count BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH
E1 AS
(
SELECT n = 1 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
), -- 1E1 rows
E2 AS (SELECT X.n FROM E1 X CROSS JOIN E1), -- 1E2 rows
E4 AS (SELECT X.n FROM E2 X CROSS JOIN E2), -- 1E4 rows
E8 AS (SELECT X.n FROM E4 X CROSS JOIN E4), -- 1E8 rows
Numbers AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM E8)
SELECT TOP (@Count)
N.n
FROM Numbers N
ORDER BY
N.n ASC;
GO
Typical results:
-- Query 1
-- Table 'Worktable'. Scan count 3, logical reads 37344 (for the hash aggregate)
-- CPU time = 297 ms, elapsed time = 548 ms.
-- Query 2 (no sorts or hashes)
-- CPU time = 47 ms, elapsed time = 41 ms.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2010 at 5:54 am
Am I too late for the party? 😀
SELECTNum,
Chr,
REPLICATE(CHAR(65 + (Num - 1) % 26), 1 + (Num - 1) / 26) AS Peso
FROM(
SELECT1 AS Num, 'A' AS Chr UNION ALL
SELECT2 AS Num, 'B' AS Chr UNION ALL
SELECT27 AS Num, 'AA' AS Chr UNION ALL
SELECT28 AS Num, 'BB' AS Chr UNION ALL
SELECT53 AS Num, 'AAA' AS Chr UNION ALL
SELECT54 AS Num, 'BBB' AS Chr
) AS d
N 56°04'39.16"
E 12°55'05.25"
April 28, 2010 at 6:01 am
SwePeso (4/28/2010)
Am I too late for the party?
For one horrible heart-stopping moment, I thought that was your solution to the 'more interesting' problem!
I really wouldn't put it past you to do it all super-fast in five lines of code :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2010 at 6:05 am
Here are two problems?
The original and the "Excel column numbering" question?
For the original question, to keep the result within VARCHAR(8000) the maximum line number to convert is 208000 which gives you 8000 Z's.
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply