D. Patrick Caldwell on Software Engineering

I 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