Converting Decimal to Hexadecimal with T-SQL on SQL Server 2008 and 2005

Yesterday, I was doing a little maintenance on our internal bugtracker.net installation. Bug tracker uses the first column of a result set to determine the background color of the issue. I wanted to create a nice fade effect between green and red, but that requires that I convert a decimal number to hexadecimal in SQL Server.

I didn't really want to write a Dec2Hex conversion function for SQL Server because I could very well end up needing a Dec2Bin function as well (if I ever wanted to convert decimal to binary in SQL), so I just wrote a custom function to convert a decimal number to any base between 2 and 36. The same SQL custom function can convert decimal numbers to hexidecimal or decimal to binary (if you need to go the other way, look here to convert any base to decimal in SQL). Here's what that looks like:

CREATE FUNCTION ConvertToBase
(
    @value AS BIGINT,
    @base AS INT
) RETURNS VARCHAR(MAX) AS BEGIN

    -- some variables
    DECLARE @characters CHAR(36),
            @result VARCHAR(MAX);

    -- the encoding string and the default result
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
           @result = '';

    -- make sure it's something we can encode.  you can't have
    -- base 1, but if we extended the length of our @character
    -- string, we could have greater than base 36
    IF @value < 0 OR @base < 2 OR @base > 36 RETURN NULL;

    -- until the value is completely converted, get the modulus
    -- of the value and prepend it to the result string.  then
    -- devide the value by the base and truncate the remainder
    WHILE @value > 0
        SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,
               @value = @value / @base;

    -- return our results
    RETURN @result;

END

Here is a test query:

SELECT
   dbo.ConvertToBase(406, 2)   -- 110010110
  ,dbo.ConvertToBase(406, 3)   -- 120001
  ,dbo.ConvertToBase(406, 8)   -- 626
  ,dbo.ConvertToBase(406, 10)  -- 406
  ,dbo.ConvertToBase(406, 16)  -- 196
  ,dbo.ConvertToBase(406, 36); -- ba