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

8 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
  6. Select dbo.ConvertFromBase('20',4) its giving 8.How 8 would be treated as base 4 no in reality?

    ReplyDelete
  7. I made this website
    http://www.binarydecimal.com/
    It can convert any numeral system from one to all others...
    Example
    http://www.binarydecimal.com/ff00ff-hexadecimal/

    ReplyDelete

  8. PLEASE READ!!!! PLEASE READ!!!! PLEASE READ!!!! PLEASE READ!!!!

    Hey Guys!!!Am so happy I got mine from Mike Fisher. My blank ATM card can withdraw € 2,000 daily. I got it from Her last week and now I have €14,000 . The blank ATM withdraws money from any ATM machines and there is no name on it, it is not traceable and now i have money for business and enough money for me and my family to live on . I am really happy i met Mike Fisher because i met two people before her and they took my money not knowing that they were scams. But am happy now. Mike Fisher sent the card through DHL and i got it in two days. Get your own card from her now she is not like other scammer pretending to have the ATM card, She is giving it out for to help people even if it is illegal but it helps a lot and no one ever gets caught. i'm grateful to Mike Fisher because she changed my story all of a sudden . The card works in all countries except, Mali and Nigeria. Mike Fisher email address is : int.hackers002@gmail.com

    ReplyDelete