Conversiones binario, octal y hexadecimal a decimal en SQL Server

CREATE FUNCTION dbo.udf_base_a_decimal(@S varchar(250), @Base int)

RETURNS bigint

AS

BEGIN

DECLARE @Result bigint, @i int, @P int,

@NumChars varchar(50)

— Declaramos las variables

SET @NumChars = ‘0123456789ABCDEF’

SET @Result = –1

SET @S = UPPER(@S)

SET @i = 1

— Validamos las bases

IF ( (@Base = 2) OR (@Base = 8) OR (@Base = 16) OR (@Base = 10) )

BEGIN

SET @Result = 0

— Iniciamos el ciclo

WHILE ( @i <= LEN(@S) )

BEGIN

SET @P = CHARINDEX(SUBSTRING(@S,@i,1),@NumChars)

IF ( (@P = 0) OR (@P > @Base) )

BEGIN

SET @Result = –1

BREAK

END

SET @Result = @Result + (@P – 1) * (POWER(@Base,(LEN(@S) – @i)))

SET @i = @i + 1

END

END

RETURN @Result

END

GO

SELECT dbo.udf_base_a_decimal(‘1000111010100’,2)

SELECT dbo.udf_base_a_decimal(‘10724’, 8)

SELECT dbo.udf_base_a_decimal(’11D4′, 16)

——————–
4564

(1 row(s) affected)

——————–
4564

(1 row(s) affected)

——————–
4564

(1 row(s) affected)