November 7, 2013 at 12:38 am
Comments posted to this topic are about the item Convert int to Excel column name
November 7, 2013 at 6:36 am
I am at a loss on this one. Can you walk thru an example of this query's utility (usefulness)?
OK, disregard, I see what the intentions are. Just never really needed this type of ordering, but would come in handy if needed.
Thank you for sharing,
Tom
November 7, 2013 at 9:01 am
Thanks Peter, I like the script, even when I can't see where to use it right now.
I've extended it to support up to the column index 16900 (XYZ).
/*
Original script: Convert int to Excel column name By Peter Diplaros
http://www.sqlservercentral.com/scripts/Excel/103688/
CGZ:
Extended for column indexes from 1 (A) to 16900 (XYZ).
Examples:
77 -> BY
495 -> SA
2771 -> DBO
6468 -> INT
13298 -> SQL
13926 -> TOP
14916 -> VAR
*/
DECLARE @ColumnIndex INT;
SET @ColumnIndex = 13298;
; WITH N26 AS (
SELECTTOP 26 N = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM master.sys.columns A
)
SELECT @ColumnIndex '@ColumnIndex',
CASE WHEN N1.N <= 2 THEN N'' ELSE NCHAR(UNICODE(N'A') + N1.N - 3) END
+ CASE WHEN N2.N = 1 THEN N'' ELSE NCHAR(UNICODE(N'A') + N2.N - 2) END
+ NCHAR(UNICODE(N'A') + N3.N - 1)
FROM N26 N1
CROSS JOIN N26 N2
CROSS JOIN N26 N3
WHERE@ColumnIndex = ((N1.N - 2) * 26 * 26) + ((N2.N - 1) * 26) + N3.N
AND @ColumnIndex >= 1
Greetings to all!!
November 7, 2013 at 10:28 am
This seems rather a complex approach -Is there any reason in particular as to why a CTE is used (e.g. Performance Benefit)
The following solution uses only arithmetic and goes all the way up to ZZ (The CTE solution only goes to YZ):
DECLARE @ColumnIndex INT = 676
Select
Case
When @ColumnIndex = 0 Then N'00'
When (@ColumnIndex <= 26) Then NChar(64 + @ColumnIndex)
When (@ColumnIndex < 702) Then NChar(64 + ((@ColumnIndex-1) / 26)) + NChar(65 + ((@ColumnIndex-1) % 26))
When (@ColumnIndex = 702) Then N'ZZ'
Else N'!!' -- Error
End
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply