Function for converting hex (hexadecimal) characters to int (integer) in SQL Server

Hex to integer in SQL Server

While there is more than one way of accomplishing this, some of them have limitations, which can be unacceptable in certain cases. The function below offers a simple convenience of input-output, accepting a string of up to 16 hexadecimal characters and returning a big integer as the result. For the opposite, please see int to hex in SQL Server.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[HexToInt]
(
	@HexValue varchar(16)
)
RETURNS bigint
AS
BEGIN

	declare @Len as int
	declare @Index as int
	declare @Char as int
	declare @Output as bigint
	declare @Base as bigint
	declare @Exp as int	
	set @Base = 16
	set @Len = len(@HexValue)
	set @Index = 0
	set @Output = 0
	set @Exp = @Len-1
	while @Index<@Len
	begin
		set @Index = @Index + 1
		set @Char = ASCII(substring(@HexValue, @Index,1))		
		if @Char >=48 AND @Char <=57
		begin
			set @Char = @Char - 48 --0 is hex 0
		end
		else if @Char >=65 AND @Char <=70
		begin
			set @Char = @Char - 55 --A is hex 10
		end
		else if @Char >=97 AND @Char <=122
		begin
			set @Char = @Char - 87 --a is hex 10
		end
		else
		begin
			--unrecognized input
			break
		end
		set @Output = @Output + @Char * power(@Base,@Exp)
		set @Exp = @Exp - 1		
	end
		
	return @Output
END





Information Error Confirmation required