Tuesday, May 5, 2009

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

Hexadecimal ShirtYesterday, 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

18 comments:

  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

    ReplyDelete
  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.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  6. I have read your blog its very attractive and impressive. I like your blog MSBI online training Hyderabad

    ReplyDelete
  7. Do you have any information regarding the license covering the code on your blog?

    ReplyDelete
  8. very nice, i wonder y not sql have this prebaked in there default provided functions.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. wow what is this really? Why aren't you doing this now? I think it's so awesome and awesome I have to 순천출장아로마
    여수출장아로마
    익산출장아로마
    정읍출장아로마
    남원출장아로마 share this with my friends and my son and wife right now I feel like I found an oasis in the desert Thank you so much for finding your site.

    ReplyDelete
  12. Hi, this is really a very informative article on Hexadecimal. Thanks for sharing this. Could you please post your next article on Splunk Certification.

    Thanks

    ReplyDelete