Ouch this hurt my brain and took me an hour...but it is slick!

This converts from any base to any base, like hex to decimal or octal.

/*

William Talada

Define baseX and baseY

Pass in a baseX value and it gets converted to baseY

Currently set to convert hex to binary but can handle any imaginary base.

*/

declare

@ina varchar(50),

@baseX varchar(30),

@baseY varchar(30)

set @ina = 'FF';

set @baseX = '0123456789ABCDEF';-- must always start with zero even if never used (column placeholder for numbers like 20, 130, etc).

set @baseY = '01';-- must always start with zero even if never used (column placeholder).

declare

@i int,

@in10 int,

@div10 int,

@outB varchar(50),

@RadixA int,

@RadixB int;

set @outB = '';

set @RadixA = len(@baseX);

set @RadixB = len(@baseY);

-- first convert @ina to @in10, base10

set @ina = reverse(@inA);

set @i = 1;

set @in10 = 0;

while @i <= len(@inA)

begin

set @in10 = @in10 + ((charindex(substring(@inA, @i, 1), @baseX) - 1) * power(@radixA, @i-1))

set @i = @i + 1;

end

select @in10;

-- now convert to new base

set @i = 1;

while (@in10 / power(@radixB, @i)) > 0

set @i = @i + 1;-- calculate number of columns needed for destination radix.

set @i = @i - 1;-- overshot so back up.

While @i >= 0

begin

set @div10 = @in10 / power(@radixB, @i);-- get magnitude of left most digit in destination radix.

set @in10 = @in10 - (@div10 * power(@radixB, @i));-- set remainder

set @outB = @outB + substring(@baseY, @div10+1, 1);-- build output string

set @i = @i - 1-- calc next lower magnitude

end

select @outB;