Convert int to Excel column name

  • Peter Diplaros

    SSC Rookie

    Points: 36

    Comments posted to this topic are about the item Convert int to Excel column name

  • tskelley

    SSCommitted

    Points: 1869

    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

  • CGZ

    Mr or Mrs. 500

    Points: 511

    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 (

    SELECT TOP 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!!

  • david.conlan 1340

    Grasshopper

    Points: 24

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply