Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

The Excel Column Name assigment problem

Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel.

DECLARE @Value BIGINT = 8839,
        @Base TINYINT = 26
 
;WITH cteSequence(Position, Value, Chr)
AS (
    SELECT  CAST(LOG(@Value - @Value / @Base) / LOG(@Base) AS INT),
            CAST(@Value - 1 AS BIGINT),
            CAST(CHAR(65 +(@Value - 1) % @Base) AS VARCHAR(MAX))
 
    UNION ALL
 
    SELECT  Position - 1,
            Value / @Base - 1,
            CHAR(65 +(Value / @Base - 1) % @Base) + Chr
    FROM    cteSequence
    WHERE   Position > 0
)
SELECT  Chr
FROM    cteSequence
WHERE   Position = 0

Print | posted on Wednesday, April 28, 2010 3:45 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# re: The Excel Column Name assigment problem

Without any context, it is pretty hard to know what this algorithm is useful (or not) for. Are you generating column names to be used in creating an excel spreadsheet? Is @Base the number of colums ? Is @Value a seed (meaning it could be set to anything). An explaination would be helpful unless this is part of a private code library.
5/2/2010 9:11 AM | Nathan Omukwenyi
Gravatar

# re: The Excel Column Name assigment problem

The original problem was to solve a "bullet numbering" problem in a XML document.
Then I realized this also could be used to mimic the "Excel colum name" assignment.

To do that, use @Base as 26 (as there are 26 letters in alphabet used by Excel.
For Excel versions less than 2007, you can use @Value between 1 and 256. For Excel version 2007 and later, you can use value between 1 and 16384.
5/2/2010 10:52 AM | Peso
Gravatar

# re: The Excel Column Name assigment problem

Thanks,useful for me.
5/7/2010 5:38 AM | Vampal
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET