Convert a number to letters

Given a number, how do you convert that t开发者_运维技巧o letters?

You can just translate your existing logic into a t-sql scalar function, like so:

CREATE FUNCTION dbo.fnColumnNameFromIndex(@i int)
RETURNS varchar(3)
AS
BEGIN
DECLARE @dividend int, @letters varchar(3), @modulo int
    SET @dividend = @i
    SET @letters = ''

    WHILE @dividend > 0
    BEGIN
        SET @modulo = (@dividend - 1) % 26
        SET @letters = CHAR(65 + @modulo) + @letters
        SET @dividend = CONVERT(int, (@dividend - @modulo) / 26 )
    END

    RETURN @letters;
END
GO

Call it like this:

SELECT dbo.fnColumnNameFromIndex(2000)

...admittedly not set logic, but I don't see this as a set logic problem.