Converting Hexadecimal or Binary to Decimal on SQL Server 2008 and 2005 ~ D. Patrick Caldwell on Software Engineering

Tuesday, May 5, 2009

Converting Hexadecimal or Binary to Decimal on SQL Server 2008 and 2005

Binary ShirtI just finished a blog post about Converting Decimal to Hexadecimal with T-SQL on SQL Server 2008 and 2005 and it got me thinking, "What if you have a hexadecimal string or a binary string and you want to convert it back to decimal?"

Well, in my previous article, I provided a SQL custom function that would allow you to convert from base 10 (decimal) to any other base you wanted. That way, you wouldn't need a Dec2Bin and a Dec2Hex function. Similarly, I don't think you should have both a Bin2Dec and a Hex2Dec to convert from binary or hexadecimal to decimal.

So, I wrote a custom function that converts from any base to base 10 and it looks a little like this:
CREATE FUNCTION ConvertFromBase
(
@value AS VARCHAR(MAX),
@base AS BIGINT
) RETURNS BIGINT AS BEGIN

-- just some variables
DECLARE @characters CHAR(36),
@result BIGINT,
@index SMALLINT;

-- initialize our charater set, our result, and the index
SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
@result = 0,
@index = 0;

-- make sure we can make the base conversion. there can't
-- be a base 1, but you could support greater than base 36
-- if you add characters to the @charater string
IF @base < 2 OR @base > 36 RETURN NULL;

-- while we have characters to convert, convert them and
-- prepend them to the result. we start on the far right
-- and move to the left until we run out of digits. the
-- conversion is the standard (base ^ index) * digit
WHILE @index < LEN(@value)
SELECT @result = @result + POWER(@base, @index) *
(CHARINDEX
(SUBSTRING(@value, LEN(@value) - @index, 1)
, @characters) - 1
),
@index = @index + 1;

-- return the result
RETURN @result;

END
Here's a test query:
SELECT
dbo.ConvertFromBase('110010110', 2) -- 406
,dbo.ConvertFromBase('120001', 3) -- 406
,dbo.ConvertFromBase('626', 8) -- 406
,dbo.ConvertFromBase('406', 10) -- 406
,dbo.ConvertFromBase('196', 16) -- 406
,dbo.ConvertFromBase('ba', 36); -- 406
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. Both of these functions are incredibly useful - so much so that I have posted links to here on my own blog (just in case I need them again!). Thanks a lot!

    ReplyDelete
  2. Merlin,

    Thanks a lot! I really appreciate your comment; I get very few of them so it's nice to know someone's getting something good out of my blog.

    As the person responsible for selecting technical resources for my company, I've found that the best candidates are active bloggers. It show's me that they have an interest in their craft, so I encourage folks to link (even shamelessly) to their own blogs in their comments on my blog. So if anybody is interested in Microsoft CRM, please take a look at Merlin's blog CRM Wizard.

    Patrick

    ReplyDelete
  3. Great function. Helped me with analyzing some Forefront TMG logs. Thanks!

    ReplyDelete
  4. Great.....solution
    as when I was using a code where I am reading hex string value from a table and passing that value to in-built function master.dbo.fn_cdc_hexstrtobin(column_name)...
    then it was returning me "Null" for the value '0x20002' I dont know why...
    But thanks for your solution...its working fine...only I have to pass the value without '0x'...
    Thanks

    ReplyDelete
  5. thanks for sharing. Its such a useful function

    ReplyDelete