Question: how to convert hex to decimal

Recently I read a question on a forum: how to convert hex to a decimal, given that it has to be in a select statement, which is executed often and it has to give good performance.

First, here are a few ways to do it:

* some inline conversion
* a UDF
* cursor
* other

Well, from the performance perspective – the cursor option is out. And so is the inline conversion. (I’d give an example of how NOT to do it, but then there might be someone who will copy the code without reading the entire post and then we have a performance problems all over. 🙂 )

Here is one good solution. Feel free to comment or suggest better ways 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), @output=@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 And here is the test: select dbo.fn_HexToIntnt('2AF3') should return 10995.

Feodor Georgiev