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
Legacy Comments
|
Nathan Omukwenyi
2010-05-02 |
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. |
|
Peso
2010-05-02 |
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. |
|
Vampal
2010-05-07 |
re: The Excel Column Name assigment problem Thanks,useful for me. |