Converting Binary Numbers To Hex

  • Comments posted to this topic are about the item Converting Binary Numbers To Hex

  • Yes, it is an interesting topic with some useful applications
    I did a bit on this a while back in Bitwise Operations in TSQL

    Best wishes,
    Phil Factor
    Simple Talk

  • Phil Factor - Tuesday, November 27, 2018 5:45 AM

    Yes, it is an interesting topic with some useful applications
    I did a bit on this a while back in Bitwise Operations in TSQL

    Great work especially with the consideration for datatype and 2's compliment.  I'm thinking you missed the point of Steve's article when it comes to the use of loops, though. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I'm curious, Steve.  With the understanding that the largest positive value you can calculate for BIGINT only occupies 63 bits (and you are only taking positive values into consideration in your conversion and the 64th bit from the right for BIGINTs would be the sign bit), why have you assigned @Bin a datatype of VARCHAR(MAX)?

    And, no... I wouldn't recommend to anyone to use Mark's function and not just because it has a WHILE loop in it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, November 27, 2018 9:23 AM

    I'm curious, Steve.  With the understanding that the largest positive value you can calculate for BIGINT only occupies 63 bits (and you are only taking positive values into consideration in your conversion and the 64th bit from the right for BIGINTs would be the sign bit), why have you assigned @Bin a datatype of VARCHAR(MAX)?

    And, no... I wouldn't recommend to anyone to use Mark's function and not just because it has a WHILE loop in it.

    I wasn't thinking of bit size, just of storing a long string. It could be a more limited datatype, and I don't know if it matters. If this is in memory as storage, is there a downside?

    BTW, the person who needed this actually had a larger than 64 bit string, so my solution didn't work for them. Not sure what they're doing, or if they just have some mistake in there. I didn't want to do the string conversion, because that's a pain. I had to do that in university, multiply 64 bit numbers at a time when we had 16 bit architectures. Not fun.

  • Hi Steve. Interesting function (I like it more than the one you adapted it from). I found one bug: it does not handle negative numbers. In order to handle negative values, the algorithm has to account for bit # 64. Both your function and the one that you linked to (from Mark) both get arithmetic overflows when including the 64th bit.

    That said, I was able to make some improvements to increase performance:

    • use VARCHAR(64) instead of VARCHAR(MAX) as the input datatype. There is a performance hit when using the MAX types, yet here the incoming string can never be more than 64 characters long
    • convert it to an inline TVF (you put in the effort to make it set-based, so why not, right?)
    • add WITH SCHEMABINDING
    • give the Query Optimizer a hint by capping the inline numbers table (i.e. CTE # 1 -- "myTally") at 64 rows since that is the max that can be used
    • remove "values" 9 and 10 from the "myTally" as they will never be used: 8 values cross-joined to 8 values gives us the max 64 rows.
    • use VARBINARY(8) instead of VARBINARY(100) in the CONVERT since we are dealing with BIGINT which is 8 bytes, so the resulting binary value can't be more than 8.
    • and not sure how much this helps, but I changed the "NULL" to "0" in the "(ORDER BY (SELECT 0))" for the ROW_NUMBER function. I figure that a zero is non-ambiguous for it's datatype (parsed as an INT) whereas NULL could be any type.

    And that leaves us with:

    CREATE OR ALTER FUNCTION dbo.BinaryToHex
    (@bin VARCHAR(64))
    RETURNS TABLE
    WITH SCHEMABINDING
    AS RETURN

      WITH myTally (n)
      AS
    -- SQL Prompt formatting off
    (SELECT TOP (64) n = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
     FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8)) a(n)
      CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8)) b(n)
    ),
    -- SQL Prompt formatting on
       binCTE (i)
      AS (SELECT POWER(CAST(2 AS BIGINT), n - 1)
         *
         CAST(SUBSTRING(REVERSE(@bin), n, 1) as BIGINT)
       FROM myTally
       WHERE n <= LEN(@bin))
      SELECT CONVERT(VARBINARY(8), SUM(i)) AS [HexVal]
        FROM binCTE
      ;
    GO

    Earlier this year I included functions to convert between a Base-2 string and BIGINT in the Free version of SQL#Convert_Base2ToBase10 and Convert_Base2ToBase10. These two functions do properly handle the 64th bit and hence negative values:

    DECLARE @TestVal VARCHAR(64);
    SET @TestVal = '111111111111111111111111111111111111111111111111111111111111111'; -- 63 digits

    SELECT [HexVal] FROM dbo.BinaryToHex(@TestVal); -- 0x7FFFFFFFFFFFFFFF
    SELECT CONVERT(VARBINARY(8), SQL#.Convert_Base2ToBase10(@TestVal)); -- 0x7FFFFFFFFFFFFFFF

    SET @TestVal = '1000000000000000000000000000000000000000000000000000000000000000'; -- 64 digits

    SELECT [HexVal] FROM dbo.BinaryToHex(@TestVal);
    -- Arithmetic overflow error converting expression to data type bigint.
    SELECT CONVERT(VARBINARY(8), SQL#.Convert_Base2ToBase10(@TestVal)); -- 0x8000000000000000

    SET @TestVal = '1111111111111111111111111111111111111111111111111111111111111111'; -- 64 digits

    SELECT [HexVal] FROM dbo.BinaryToHex(@TestVal);
    -- Arithmetic overflow error converting expression to data type bigint.
    SELECT CONVERT(VARBINARY(8), SQL#.Convert_Base2ToBase10(@TestVal)); -- 0xFFFFFFFFFFFFFFFF

    -- Verify:
    SELECT CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFF);
    -- returns:  -1

    Finally, in order to help folks better understand how the 1001100... syntax relates to the BIGINT value, I provided a visual representation of it here:
    Binary / Base2 / BitMask Notes

    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Thanks and that's a good point. I'll update the article to note this is positives only.

  • Solomon Rutzky - Tuesday, November 27, 2018 9:38 AM

    • and not sure how much this helps, but I changed the "NULL" to "0" in the "(ORDER BY (SELECT 0))" for the ROW_NUMBER function. I figure that a zero is non-ambiguous for it's datatype (parsed as an INT) whereas NULL could be any type.

    Unless CAST (either implicitly or explicitly), a literal NULL is an int.  There is a lot of implicit casting that happens, and as far as I can tell from experimentation the compiler does a pretty good job of inferring an appropriate type for the NULL.  That said, SELECT NULL AS A INTO #temp will create column A as an int.

  • t.ovod-everett - Tuesday, November 27, 2018 11:02 AM

    Solomon Rutzky - Tuesday, November 27, 2018 9:38 AM

    • and not sure how much this helps, but I changed the "NULL" to "0" in the "(ORDER BY (SELECT 0))" for the ROW_NUMBER function. I figure that a zero is non-ambiguous for it's datatype (parsed as an INT) whereas NULL could be any type.

    Unless CAST (either implicitly or explicitly), a literal NULL is an int.  There is a lot of implicit casting that happens, and as far as I can tell from experimentation the compiler does a pretty good job of inferring an appropriate type for the NULL.  That said, SELECT NULL AS A INTO #temp will create column A as an int.

    I was guessing it would be INT as that is the easiest, most sensible choice, but wasn't sure. Thanks for doing that test to prove it :). I will still use 0 as it seems more readable, even if only a little bit (to me, at least).

    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Solomon Rutzky - Tuesday, November 27, 2018 2:54 PM

    I was guessing it would be INT as that is the easiest, most sensible choice, but wasn't sure. Thanks for doing that test to prove it :). I will still use 0 as it seems more readable, even if only a little bit (to me, at least).

    For what it's worth, I took the query in https://stackoverflow.com/questions/10066819/what-does-order-by-select-null-mean and generated estimated execution plans for it with ORDER BY (SELECT NULL) and ORDER BY (SELECT 0) under SQL 2016.  I saved the XML plans and diffed them and - voila - identical plans (well, except for insignificant variation in CompileTime, CompileCPU, and MaxCompileMemory).

    With that in mind, the one advantage I can see for ORDER BY (SELECT NULL) is that there's a cool blog by that name: https://orderbyselectnull.com/about/ .

  • Steve Jones - SSC Editor - Tuesday, November 27, 2018 9:35 AM

    Jeff Moden - Tuesday, November 27, 2018 9:23 AM

    I'm curious, Steve.  With the understanding that the largest positive value you can calculate for BIGINT only occupies 63 bits (and you are only taking positive values into consideration in your conversion and the 64th bit from the right for BIGINTs would be the sign bit), why have you assigned @Bin a datatype of VARCHAR(MAX)?

    And, no... I wouldn't recommend to anyone to use Mark's function and not just because it has a WHILE loop in it.

    I wasn't thinking of bit size, just of storing a long string. It could be a more limited datatype, and I don't know if it matters. If this is in memory as storage, is there a downside?

    BTW, the person who needed this actually had a larger than 64 bit string, so my solution didn't work for them. Not sure what they're doing, or if they just have some mistake in there. I didn't want to do the string conversion, because that's a pain. I had to do that in university, multiply 64 bit numbers at a time when we had 16 bit architectures. Not fun.

    I was just considering the reason why I didn't ever publish the MAX version of DelimitedSplit8K... LOBs don't like the JOIN between the Tally object and the LOB at the substring level and tend to be 2 to 3 times slower just by using MAX even if it contains a value less than 8K bytes.  I've not proven it specifically with your function but I've also seen where not rightsizing inputs to functions does cause excessive memory allocation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • One small correction I see.  In the section "Setting Up a Test" you have your initial version of the BinaryToHex function.  I believe for this to work as written you'll want to change the calculation of the @return value from

    SET @return = CONVERT(VARBINARY(100), 15)

    to

    SET @return = CONVERT(VARBINARY(100), @bin)

     

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply