Has it ever happened to you that you say something but forget to follow up due to any reason? It usually does not happen to me as I try to remember everything in my task list but there is always an exception. Last year I asked a question regarding about how to convert Hex to Decimal. I promised that I will post an answer with Due Credit to the author but never got around to post a blog post around it. Read the original post over here SQL SERVER – Question – How to Convert Hex to Decimal.
The matter of the fact, I received excellent answers to my earlier questions and the blog post has been excellent resource of the solution when it someone wants to convert Hex to Decimal.
The hex value of int 256 is 0x00000100. We will use the same for all of our examples.
Let us go over some of the contributions and solutions:
1) Solution by Fly
Conversion from INT to HEX
SELECT CONVERT(VARBINARY(8), 256)
Converting from HEX to INT
SELECT CONVERT(INT, 0x00000100)
2) Solution by Madhivanan
Madhivanan has written very interesting note regarding how Hex can be converted to Int.
SELECT 0x00000100*1 HextoInt
You just have to multiply Hex with *1 and it will convert it to Int.
He has written a follow up blog post on the same subject.
DECLARE @hex VARCHAR(64) = '0x00000100'
SELECT CAST( CONVERT(VARBINARY,'0x'+RIGHT('00000000'+REPLACE(@hex,'x',''),8),1) AS INT)
4) Solution by SPV
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00000100', 1))
Another Remarkable Solutions
Feodor has excellent solution where he has created a function which can do this task.
Marko Parkkola has come up with an excellent solution with CLR.
Well, it was an interesting experience and I had really fun experimenting with all the solutions.
Which one is your favorite solution out of all the above solutions?
Reference: Pinal Dave (https://blog.sqlauthority.com)