[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Get ExcelColumn-Char from Number

Posted on 2007-11-23
Medium Priority
258 Views
I'm pretty new with SQL-Server and never created a custom function.
I have a table column (int) which represents an Excel ColumnID. Now I need to display the column header.

1=A
2=B
26=Z
27=AA
28=AB....

Can anybody help.
Chris
0
Question by:chriswies
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 3
• 2
• +1

LVL 6

Accepted Solution

PaultheBroker earned 2000 total points
ID: 20338451
I've done this as a function as requested, but could also just be included in the SQL
``````CREATE FUNCTION dbo.ColHeader_fn
( @Int 	integer )
RETURNS varchar(2)
AS
BEGIN
if @int>26 set @ColHeader = char(64+@int/26) + char(64+@int-(26*(@int/26)))
END

--USAGE
``````
0

LVL 18

Expert Comment

ID: 20338457
Try this:

Hope this helps ...
``````declare @Y int
set     @Y = 55

select  @Y
,       case
when @Y <= 26 then char(@Y+64)
else char(((@Y-1)/26)+64) + char(((@Y-1)%26)+65)
end
``````
0

LVL 6

Expert Comment

ID: 20338467
Here is the SQL (assuming the table column with the Excel ColumnID is called [Integer]
``````[ColHeader] =
case when [Integer] > 26 then char(64+[Integer]/26) + char(64+[Integer]-(26*([Integer]/26)))
else char(64+@int) end
``````
0

Author Comment

ID: 20338481
amazing, great
thank you Yveau
0

LVL 6

Expert Comment

ID: 20338516
hmmmph !
0

Author Comment

ID: 20338547
hmmmmph!
understand, that was my fault.
Actually I want to go with the function. I hit accept on the 2nd last and that was not your solution. I didn't see your 2nd post at that time.
What is EE rule for that?
How can I go from here?
Chris
0

LVL 6

Expert Comment

ID: 20338616
Thanks for that....!!  I think you need to post a question in the Community Support, asking the Moderator to change the point allocation.  Include a link to your original question.
0

LVL 18

Expert Comment

ID: 20338632
0

LVL 6

Expert Comment

ID: 20343449
From Yveau's solution I notice the use of the % operand - I was looking for hte MOD function - and now I've found it!!  For completeness, I've updated my solution to incorporate my new found knowledge !!!   It actually makes the equation nice and symmetrical :)  BTW: did you ever get anywhere on having the point allocation changed???
-----------------
( @Int       integer )
RETURNS varchar(2)
AS
BEGIN
if @int>26 set @ColHeader = char(64+@int/26) + char(64+@int%26)
END
0

LVL 1

Expert Comment

ID: 20345574

Vee_Mod
Experts Exchange Moderator
0

LVL 6

Expert Comment

ID: 20345656
Call me a perfectionist, but the IF..THEN... (or case ...when) statement has been bugging me as being somehow 'inelegant'.  So I've reworked it slightly so the whole operation is performed in a single operation.....enjoy!
``````ALTER FUNCTION dbo.ColHeader_fn
( @Int       integer )
RETURNS varchar(2)
AS
BEGIN
END
``````
0

Author Comment

ID: 20917885
Hi,
I received a but report from a user that had a column 52 which is AZ but the function deliviered B@.
So I reviewed the function and found that actually the logic of Yveau brings up the right col.
Thanks Yveau!!

That is how the function looks right now:
( @Int       integer )
RETURNS varchar(2)
AS
BEGIN
if @int>26 set @ColHeader = char(((@int-1)/26)+64) + char(((@int-1)%26)+65)
END
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month14 days, 15 hours left to enroll