How to Convert Hex to Decimal

In one of the recent projects, I realize the bottleneck of the query was an inline function which was converting Hex to Decimal. I optimized the inline function and reduced the query running time to one-tenth of the original running time. Later, I was eager to find out the script my blog readers might be using for hex to decimal conversion. Please leave your comments here and I will consider all the valid answers and publish with due credit to the author in one of the future posts. If the script you have posted here is not your original script, I suggest that you include the source as well.

Here is one way to do it:

create function fn_HexToIntnt(@str varchar(16))
returns bigint as begin

select @str=upper(@str)
declare @i int, @len int, @char char(1), @output bigint

select @len=len(@str)
,@i=@len
,@output=case

when
@len>0
then 0
end

while (@i>0)
begin
select
@char = substring(@str,@i,1),
@outpu t= @output +(ASCII(@char) -
(case when @char between ‘A’ and ‘F’ then 55
else
case when @char between ‘0′ and ‘9′ then 48
end
end))
*power(16.,@len-@i)
,@i=@i-1
end
return @output
end

hmm..
What about using built-in function Convert?

SELECT CONVERT(INT, 0×00000100)
SELECT CONVERT(VARBINARY(8), 256)

I do not pretend to be the author, but i am using this for a long time

Read more:  Journey to SQL Authority with Pinal Dave

Posted via email from jasper22's posterous