Skip to content

Instantly share code, notes, and snippets.

@mlt
Last active February 5, 2023 18:40
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mlt/2e26dda28dae2dac9a345e35834c5526 to your computer and use it in GitHub Desktop.
Save mlt/2e26dda28dae2dac9a345e35834c5526 to your computer and use it in GitHub Desktop.
Calculate MS Excel column name from its number in PostgreSQL
CREATE FUNCTION excel_column(col integer)
RETURNS text AS
$BODY$
WITH RECURSIVE t(n, out) AS (
SELECT col/26-(col%26=0)::int, chr((col-1)%26 + 65)
UNION ALL
SELECT n/26-(n%26=0)::int, chr((n-1)%26 + 65) || out FROM t
where n>0
)
SELECT out FROM t where n=0;
$BODY$
LANGUAGE sql IMMUTABLE LEAKPROOF STRICT;
select excel_column(x) from generate_series(1,800) x;
"A"
"B"
"C"
"D"
"E"
"F"
"G"
"H"
"I"
"J"
"K"
"L"
"M"
"N"
"O"
"P"
"Q"
"R"
"S"
"T"
"U"
"V"
"W"
"X"
"Y"
"Z"
"AA"
"AB"
"AC"
"AD"
"AE"
"AF"
"AG"
"AH"
"AI"
"AJ"
"AK"
"AL"
"AM"
"AN"
"AO"
"AP"
"AQ"
"AR"
"AS"
"AT"
"AU"
"AV"
"AW"
"AX"
"AY"
"AZ"
"BA"
"BB"
"BC"
"BD"
"BE"
"BF"
"BG"
"BH"
"BI"
"BJ"
"BK"
"BL"
"BM"
"BN"
"BO"
"BP"
"BQ"
"BR"
"BS"
"BT"
"BU"
"BV"
"BW"
"BX"
"BY"
"BZ"
"CA"
"CB"
"CC"
"CD"
"CE"
"CF"
"CG"
"CH"
"CI"
"CJ"
"CK"
"CL"
"CM"
"CN"
"CO"
"CP"
"CQ"
"CR"
"CS"
"CT"
"CU"
"CV"
"CW"
"CX"
"CY"
"CZ"
"DA"
"DB"
"DC"
"DD"
"DE"
"DF"
"DG"
"DH"
"DI"
"DJ"
"DK"
"DL"
"DM"
"DN"
"DO"
"DP"
"DQ"
"DR"
"DS"
"DT"
"DU"
"DV"
"DW"
"DX"
"DY"
"DZ"
"EA"
"EB"
"EC"
"ED"
"EE"
"EF"
"EG"
"EH"
"EI"
"EJ"
"EK"
"EL"
"EM"
"EN"
"EO"
"EP"
"EQ"
"ER"
"ES"
"ET"
"EU"
"EV"
"EW"
"EX"
"EY"
"EZ"
"FA"
"FB"
"FC"
"FD"
"FE"
"FF"
"FG"
"FH"
"FI"
"FJ"
"FK"
"FL"
"FM"
"FN"
"FO"
"FP"
"FQ"
"FR"
"FS"
"FT"
"FU"
"FV"
"FW"
"FX"
"FY"
"FZ"
"GA"
"GB"
"GC"
"GD"
"GE"
"GF"
"GG"
"GH"
"GI"
"GJ"
"GK"
"GL"
"GM"
"GN"
"GO"
"GP"
"GQ"
"GR"
"GS"
"GT"
"GU"
"GV"
"GW"
"GX"
"GY"
"GZ"
"HA"
"HB"
"HC"
"HD"
"HE"
"HF"
"HG"
"HH"
"HI"
"HJ"
"HK"
"HL"
"HM"
"HN"
"HO"
"HP"
"HQ"
"HR"
"HS"
"HT"
"HU"
"HV"
"HW"
"HX"
"HY"
"HZ"
"IA"
"IB"
"IC"
"ID"
"IE"
"IF"
"IG"
"IH"
"II"
"IJ"
"IK"
"IL"
"IM"
"IN"
"IO"
"IP"
"IQ"
"IR"
"IS"
"IT"
"IU"
"IV"
"IW"
"IX"
"IY"
"IZ"
"JA"
"JB"
"JC"
"JD"
"JE"
"JF"
"JG"
"JH"
"JI"
"JJ"
"JK"
"JL"
"JM"
"JN"
"JO"
"JP"
"JQ"
"JR"
"JS"
"JT"
"JU"
"JV"
"JW"
"JX"
"JY"
"JZ"
"KA"
"KB"
"KC"
"KD"
"KE"
"KF"
"KG"
"KH"
"KI"
"KJ"
"KK"
"KL"
"KM"
"KN"
"KO"
"KP"
"KQ"
"KR"
"KS"
"KT"
"KU"
"KV"
"KW"
"KX"
"KY"
"KZ"
"LA"
"LB"
"LC"
"LD"
"LE"
"LF"
"LG"
"LH"
"LI"
"LJ"
"LK"
"LL"
"LM"
"LN"
"LO"
"LP"
"LQ"
"LR"
"LS"
"LT"
"LU"
"LV"
"LW"
"LX"
"LY"
"LZ"
"MA"
"MB"
"MC"
"MD"
"ME"
"MF"
"MG"
"MH"
"MI"
"MJ"
"MK"
"ML"
"MM"
"MN"
"MO"
"MP"
"MQ"
"MR"
"MS"
"MT"
"MU"
"MV"
"MW"
"MX"
"MY"
"MZ"
"NA"
"NB"
"NC"
"ND"
"NE"
"NF"
"NG"
"NH"
"NI"
"NJ"
"NK"
"NL"
"NM"
"NN"
"NO"
"NP"
"NQ"
"NR"
"NS"
"NT"
"NU"
"NV"
"NW"
"NX"
"NY"
"NZ"
"OA"
"OB"
"OC"
"OD"
"OE"
"OF"
"OG"
"OH"
"OI"
"OJ"
"OK"
"OL"
"OM"
"ON"
"OO"
"OP"
"OQ"
"OR"
"OS"
"OT"
"OU"
"OV"
"OW"
"OX"
"OY"
"OZ"
"PA"
"PB"
"PC"
"PD"
"PE"
"PF"
"PG"
"PH"
"PI"
"PJ"
"PK"
"PL"
"PM"
"PN"
"PO"
"PP"
"PQ"
"PR"
"PS"
"PT"
"PU"
"PV"
"PW"
"PX"
"PY"
"PZ"
"QA"
"QB"
"QC"
"QD"
"QE"
"QF"
"QG"
"QH"
"QI"
"QJ"
"QK"
"QL"
"QM"
"QN"
"QO"
"QP"
"QQ"
"QR"
"QS"
"QT"
"QU"
"QV"
"QW"
"QX"
"QY"
"QZ"
"RA"
"RB"
"RC"
"RD"
"RE"
"RF"
"RG"
"RH"
"RI"
"RJ"
"RK"
"RL"
"RM"
"RN"
"RO"
"RP"
"RQ"
"RR"
"RS"
"RT"
"RU"
"RV"
"RW"
"RX"
"RY"
"RZ"
"SA"
"SB"
"SC"
"SD"
"SE"
"SF"
"SG"
"SH"
"SI"
"SJ"
"SK"
"SL"
"SM"
"SN"
"SO"
"SP"
"SQ"
"SR"
"SS"
"ST"
"SU"
"SV"
"SW"
"SX"
"SY"
"SZ"
"TA"
"TB"
"TC"
"TD"
"TE"
"TF"
"TG"
"TH"
"TI"
"TJ"
"TK"
"TL"
"TM"
"TN"
"TO"
"TP"
"TQ"
"TR"
"TS"
"TT"
"TU"
"TV"
"TW"
"TX"
"TY"
"TZ"
"UA"
"UB"
"UC"
"UD"
"UE"
"UF"
"UG"
"UH"
"UI"
"UJ"
"UK"
"UL"
"UM"
"UN"
"UO"
"UP"
"UQ"
"UR"
"US"
"UT"
"UU"
"UV"
"UW"
"UX"
"UY"
"UZ"
"VA"
"VB"
"VC"
"VD"
"VE"
"VF"
"VG"
"VH"
"VI"
"VJ"
"VK"
"VL"
"VM"
"VN"
"VO"
"VP"
"VQ"
"VR"
"VS"
"VT"
"VU"
"VV"
"VW"
"VX"
"VY"
"VZ"
"WA"
"WB"
"WC"
"WD"
"WE"
"WF"
"WG"
"WH"
"WI"
"WJ"
"WK"
"WL"
"WM"
"WN"
"WO"
"WP"
"WQ"
"WR"
"WS"
"WT"
"WU"
"WV"
"WW"
"WX"
"WY"
"WZ"
"XA"
"XB"
"XC"
"XD"
"XE"
"XF"
"XG"
"XH"
"XI"
"XJ"
"XK"
"XL"
"XM"
"XN"
"XO"
"XP"
"XQ"
"XR"
"XS"
"XT"
"XU"
"XV"
"XW"
"XX"
"XY"
"XZ"
"YA"
"YB"
"YC"
"YD"
"YE"
"YF"
"YG"
"YH"
"YI"
"YJ"
"YK"
"YL"
"YM"
"YN"
"YO"
"YP"
"YQ"
"YR"
"YS"
"YT"
"YU"
"YV"
"YW"
"YX"
"YY"
"YZ"
"ZA"
"ZB"
"ZC"
"ZD"
"ZE"
"ZF"
"ZG"
"ZH"
"ZI"
"ZJ"
"ZK"
"ZL"
"ZM"
"ZN"
"ZO"
"ZP"
"ZQ"
"ZR"
"ZS"
"ZT"
"ZU"
"ZV"
"ZW"
"ZX"
"ZY"
"ZZ"
"AAA"
"AAB"
"AAC"
"AAD"
"AAE"
"AAF"
"AAG"
"AAH"
"AAI"
"AAJ"
"AAK"
"AAL"
"AAM"
"AAN"
"AAO"
"AAP"
"AAQ"
"AAR"
"AAS"
"AAT"
"AAU"
"AAV"
"AAW"
"AAX"
"AAY"
"AAZ"
"ABA"
"ABB"
"ABC"
"ABD"
"ABE"
"ABF"
"ABG"
"ABH"
"ABI"
"ABJ"
"ABK"
"ABL"
"ABM"
"ABN"
"ABO"
"ABP"
"ABQ"
"ABR"
"ABS"
"ABT"
"ABU"
"ABV"
"ABW"
"ABX"
"ABY"
"ABZ"
"ACA"
"ACB"
"ACC"
"ACD"
"ACE"
"ACF"
"ACG"
"ACH"
"ACI"
"ACJ"
"ACK"
"ACL"
"ACM"
"ACN"
"ACO"
"ACP"
"ACQ"
"ACR"
"ACS"
"ACT"
"ACU"
"ACV"
"ACW"
"ACX"
"ACY"
"ACZ"
"ADA"
"ADB"
"ADC"
"ADD"
"ADE"
"ADF"
"ADG"
"ADH"
"ADI"
"ADJ"
"ADK"
"ADL"
"ADM"
"ADN"
"ADO"
"ADP"
"ADQ"
"ADR"
"ADS"
"ADT"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment