Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

April 2010 Blog Posts

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

posted @ Wednesday, April 28, 2010 3:45 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Powered by:
Powered By Subtext Powered By ASP.NET