Convert int to Excel column name

• Peter Diplaros

  SSC Rookie

  Points: 36

  More actions

  November 7, 2013 at 12:38 am

  #300022

  Comments posted to this topic are about the item Convert int to Excel column name

• tskelley

  SSCommitted

  Points: 1870

  More actions

  November 7, 2013 at 6:36 am

  #1664730

  I am at a loss on this one. Can you walk thru an example of this query's utility (usefulness)?

  OK, disregard, I see what the intentions are. Just never really needed this type of ordering, but would come in handy if needed.

  Thank you for sharing,

  Tom

• CGZ

  Mr or Mrs. 500

  Points: 522

  More actions

  November 7, 2013 at 9:01 am

  #1664819

  Thanks Peter, I like the script, even when I can't see where to use it right now.

  I've extended it to support up to the column index 16900 (XYZ).

  /*

  Original script: Convert int to Excel column name By Peter Diplaros

  http://www.sqlservercentral.com/scripts/Excel/103688/

  CGZ:

  Extended for column indexes from 1 (A) to 16900 (XYZ).

  Examples:

  77 -> BY

  495 -> SA

  2771 -> DBO

  6468 -> INT

  13298 -> SQL

  13926 -> TOP

  14916 -> VAR

  */

  DECLARE @ColumnIndex INT;

  SET @ColumnIndex = 13298;

  ; WITH N26 AS (

  SELECTTOP 26 N = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM master.sys.columns A

  )

  SELECT @ColumnIndex '@ColumnIndex',

  CASE WHEN N1.N <= 2 THEN N'' ELSE NCHAR(UNICODE(N'A') + N1.N - 3) END

  + CASE WHEN N2.N = 1 THEN N'' ELSE NCHAR(UNICODE(N'A') + N2.N - 2) END

  + NCHAR(UNICODE(N'A') + N3.N - 1)

  FROM N26 N1

  CROSS JOIN N26 N2

  CROSS JOIN N26 N3

  WHERE@ColumnIndex = ((N1.N - 2) * 26 * 26) + ((N2.N - 1) * 26) + N3.N

  AND @ColumnIndex >= 1

  Greetings to all!!

• david.conlan 1340

  Grasshopper

  Points: 24

  More actions

  November 7, 2013 at 10:28 am

  #1664861

  This seems rather a complex approach -Is there any reason in particular as to why a CTE is used (e.g. Performance Benefit)

  The following solution uses only arithmetic and goes all the way up to ZZ (The CTE solution only goes to YZ):

  DECLARE @ColumnIndex INT = 676

  Select

  Case

  When @ColumnIndex = 0 Then N'00'

  When (@ColumnIndex <= 26) Then NChar(64 + @ColumnIndex)

  When (@ColumnIndex < 702) Then NChar(64 + ((@ColumnIndex-1) / 26)) + NChar(65 + ((@ColumnIndex-1) % 26))

  When (@ColumnIndex = 702) Then N'ZZ'

  Else N'!!' -- Error

  End