SQL SERVER – Answer – How to Convert Hex to Decimal or INT

SQL SERVER - Answer - How to Convert Hex to Decimal or INT Hexadecimal-Clock 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.

3) Solution by Ray and knightEknight

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.

James Curran and Vedran Kesegic have excellent solution which very interesting as well.

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)

,
Previous Post
SQL SERVER – How do I Record Video and Webcast – Milestone – 2200th Blog Post – SQL in Sixty Seconds #022 – Video
Next Post
SQL SERVER – Query to Get Unique Distinct Data Based on Condition – Eliminate Duplicate Data from Resultset

Related Posts

10 Comments. Leave new

  • embeducation (@AWSOMEDEVSIGNER)
    July 26, 2012 7:03 am

    Very cool, man! Very cool. Your blog is outstanding :)

    Reply
  • Might be 3rd one…bit Interesting…. to learn some more…..

    Reply
  • the first and the second are great.

    Reply
  • 1st one for simplicity

    Reply
  • very interesting! thks

    Reply
  • Hello ,
    How can I Convert a whole column from Decimal or INT to Hex

    Reply
  • well, this looks good in theory. But if you have odd number of digits after the ‘0x’, your life wouldn’t be so easy. None of the above would work.

    Reply
  • be carreful to the maximum size of the type int.
    If you convert a Hex with a value> 2147483647 (7F FF FF FF)
    It is better to use the bigint type

    Reply
  • There are cases that you would like that function, but are not able to. For those cases a recursive CTE can be handy. The one that did the job for me:

    “`
    ;with inputSet as (
    select ‘ff’ as inputTxt
    union all
    select ‘1a’
    union all
    select ’20a’
    ), convertHexToDec as (
    select
    1 as charIdx,
    0 as decValue,
    upper(inputTxt) as inputTxt
    from inputSet
    union all
    select
    charIdx+1,
    decValue*16 + ascii(substring(inputtxt, charIdx, 1)) – case when substring(inputtxt, charIdx, 1) between ‘A’ and ‘G’ then 55 else 48 end,
    inputTxt
    from convertHexToDec
    where charIdx<=len(inputTxt)
    ), resultSet as (
    select
    max(decValue) as decValue,
    inputTxt
    from convertHexToDec
    group by inputTxt
    )
    select *
    from resultSet
    “`
    The real magic is done in the 'convertHexToDec' CTE, but the results need to be flattened, that's done in the resultset.

    Reply

Leave a Reply

Menu