Convert int to Excel column name

,

This script converts a numeric column index to an Excel column name (ex: AC). The script doesn't use cursors or any other iteration. It converts the value using only set-based code so it is very suitable for using within a function.

The @ColumnIndex parameter is 1-based (1 = column A)

DECLARE @ColumnIndex INT = 53

; WITH N26 AS (
	SELECT	TOP 26 N = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM master.sys.columns A 
)
SELECT  CASE WHEN N1.N = 1 THEN N'' ELSE NCHAR(UNICODE(N'A') + N1.N - 2) END + NCHAR(UNICODE(N'A') + N2.N - 1)
FROM    N26 N1
		CROSS JOIN N26 N2
WHERE	@ColumnIndex = ((N1.N - 1) * 26) + N2.N
ORDER	BY N1.N, N2.N

Rate

Share

Share

Rate