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:

-- 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) *
(SUBSTRING(@value, LEN(@value) - @index, 1)
, @characters) - 1
@index = @index + 1;

-- return the result
RETURN @result;

Here's a test query:
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


  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!

  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.


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

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

  5. thanks for sharing. Its such a useful function

  6. Select dbo.ConvertFromBase('20',4) its giving 8.How 8 would be treated as base 4 no in reality?