?
Solved

Get ExcelColumn-Char from Number

Posted on 2007-11-23
12
Medium Priority
?
252 Views
Last Modified: 2010-05-18
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
Comment
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
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 6

Accepted Solution

by:
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
	DECLARE @ColHeader varchar(2)
	if @int>26 set @ColHeader = char(64+@int/26) + char(64+@int-(26*(@int/26)))
	else set @ColHeader = char(64+@int)
RETURN @ColHeader
END
 
--USAGE
select dbo.ColHeader_fn(43)

Open in new window

0
 
LVL 18

Expert Comment

by:Yveau
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

Open in new window

0
 
LVL 6

Expert Comment

by:PaultheBroker
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

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:chriswies
ID: 20338481
amazing, great
thank you Yveau
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20338516
hmmmph !
0
 

Author Comment

by:chriswies
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

by:PaultheBroker
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

by:Yveau
ID: 20338632
0
 
LVL 6

Expert Comment

by:PaultheBroker
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???
-----------------
CREATE FUNCTION dbo.ColHeader_fn
      ( @Int       integer )
RETURNS varchar(2)
AS
BEGIN
      DECLARE @ColHeader varchar(2)
      if @int>26 set @ColHeader = char(64+@int/26) + char(64+@int%26)
      else set @ColHeader = char(64+@int)
RETURN @ColHeader
END
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20345574
Re-opened at Asker request.


Vee_Mod
Experts Exchange Moderator
0
 
LVL 6

Expert Comment

by:PaultheBroker
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
      DECLARE @ColHeader varchar(2)
	SET @ColHeader =REPLACE(char(64+@int/26) + char(64+@int%26),char(64),'')
RETURN @ColHeader
END

Open in new window

0
 

Author Comment

by:chriswies
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:
CREATE FUNCTION dbo.ColHeader_fn3
      ( @Int       integer )
RETURNS varchar(2)
AS
BEGIN
      DECLARE @ColHeader varchar(2)
      if @int>26 set @ColHeader = char(((@int-1)/26)+64) + char(((@int-1)%26)+65)
      else set @ColHeader = char(64+@int)
RETURN @ColHeader
END
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question