Converting Decimal to Hexadecimal with T-SQL on SQL Server 2008 and 2005 ~ D. Patrick Caldwell on Software Engineering

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
I really appreciate comments so please feel free to comment on my posts. Whether you agree or disagree, I'd love to hear from you. Also, feel free to link back to your own blog in your comments. You can even subscribe to an RSS feed of the comments on this thread.

© 2008 — , D. Patrick Caldwell, President, Autopilot Consulting, LLC

5 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