## 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

## # 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.## # 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.

## # re: The Excel Column Name assigment problem

Thanks,useful for me.