Tuesday, May 5, 2009

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`

1. This was really useful. I've used it to help me convert from decimal colours e.g. Red = 255, Green = 65280, Blue = 16711680 to their associated Hex values, 0000FF, 00FF00, FF0000 (and colour variants within)

Thanks

2. Cool! I'm glad it was useful. Thanks for letting me know how you put this solution to work. I used it for the same thing. We use BugTracker.Net as our helpdesk solution in my office. I created a nice priority fade effect so that it fades from green to yellow to red by importance. It's a nice visual effect.

Thanks a lot for commenting and I'm glad this post was helpful.

3. This comment has been removed by the author.

4. Hey there,

Thanks for posting this, but shouldn't the result be '0' (zero) if the @value = 0 (zero)?

At least that's how I tweaked it now

Sandro

5. Spent ages trolling through useless code but this works like a charm ... Thanks a million. This is real useful and great stuff.

Regards,
Mustafa Shairani

6. Awesome. works very well..