Help with extracting Bits out of Binary Number

  • I have a Hex number 0x00280000 which in binary is

    0000 0000 0010 1000 0000 0000 0000 0000

    Starting from the left...I need to convert the bits 01010 (position 10-14) into Decimal - in this case the result would be 10 (2+8)

    I'm doing (trying) this in a sql procedure in sql server 2005.

    Appreciate any help - Thanks

    Jim

  • HEX is the same as varbinary, in SQL you can load the value like so and convert to int.

    declare @x as varbinary(100)

    set @x = 0x00280000

    select cast(@x as int)

    -- demonstartes you get the same answer from the number.

    select cast(2621440 as varbinary(100))

    So unless you are reordering the bits this should work.

  • Thanks - but I'm only wanting certain bits - see original post...

    In this case the answer I'm needing is decimal 10

  • I am trying to figure out then how you make your conversion to binary (from HEX) and determine your starting position. Can you explain your logic a bit more and use more than one example. It just isn't jumping out at me.

  • I'm sure someone is going to trot out a tally table solution any minute now. That being said - doing it the old fashioned way might work....

    alter function bintoDec(@bin as varchar(100))

    Returns int

    as

    begin

    declare @idx int

    declare @result int

    set @result=0

    set @idx=1

    While @idx<=len(rtrim(@bin))

    begin

    select @Result=@result*2+cast(substring(@bin,@idx,1) as int),@idx=@idx+1

    end

    return @result

    end

    usage would look like (in your case)

    select dbo.bintodec(substring('00000000001010000000000000000000',10,5))

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Sure...Basically I have a large Decimal number ie. 168034788 which I then have to convert to Hex ie. 0A0401E4.....This represents a collection of data which has to be looked at from a binary point of view...from right to left - first 5 bits equal a, next 13 bits equal b, next 5 bits equal c, next 5 bits equal d and toss the leftmost 4 bits (total 32 bits). Each of these entities needs to be converted back into a decimal number (and they don't even line up on the byte boundry)...

    I know, I know - I didn't design it, I just have to deal with it 🙂

    BTW these values are dynamic so I really can't use the function with a hardcoded binary string...

    Clear as mud ?

  • Sounds like the time I had to try to translate some mainframe data.

    Now

    from right to left - first 5 bits equal a, next 13 bits equal b, next 5 bits equal c, next 5 bits equal d and toss the leftmost 4 bits (total 32 bits).

    How do I know 5, 13, 5, 5? Are those widths hard values or does it vary and there is something that triggers what is what?

  • First of all, you need to count the bits from the right... not the left... and they should be counted as powers of 2 starting with the exponent 0... then you can do fun stuff like the following without a tally table, etc. I'm not sure what you mean by the groups of 5, 13, etc... maybe I can work on that a bit, later, if you'd care to explain just a wee "bit" more :P... Do you expect the right most bit of each of those groups to represent 20 in your returned decimal number?

    DECLARE @BinaryTest TABLE (X VARBINARY(10))

    INSERT INTO @BinaryTest (X)

    SELECT 0x00280000 UNION ALL

    SELECT 0x006A0000 UNION ALL

    SELECT 0x00270000 UNION ALL

    SELECT 0x00370000 UNION ALL

    SELECT 0x00470000 UNION ALL

    SELECT 0xFF28FFFF

    SELECT X,

    SIGN(CAST(X AS INT) & (POWER(2,19))) AS Bit19,

    SIGN(CAST(X AS INT) & (POWER(2,21))) AS Bit21,

    CAST(X AS INT) & (POWER(2,19)+POWER(2,21)) AS DecimalValue

    FROM @BinaryTest

    --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.


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

  • I think the first step we have to address here is converting to BITS (one-byte patterns for each HEX value) so I have this

    CREATE FUNCTION HEXTOBIN (@valIn varbinary(100))

    RETURNS VARCHAR(8000)

    AS

    BEGIN

    DECLARE @valInPos int

    DECLARE @strRet varchar(8000)

    SET @valInPos = 1

    WHILE @valInPos <= len(@valIn)

    BEGIN

    SET @strRet = IsNull(@strRet,'') + (SELECT

    CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 128 = 128 THEN '1' ELSE '0' END +

    CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 64 = 64 THEN '1' ELSE '0' END +

    CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 32 = 32 THEN '1' ELSE '0' END +

    CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 16 = 16 THEN '1' ELSE '0' END +

    CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 8 = 8 THEN '1' ELSE '0' END +

    CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 4 = 4 THEN '1' ELSE '0' END +

    CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 2 = 2 THEN '1' ELSE '0' END +

    CASE WHEN cast(cast(substring(@valIn,@valInPos,1) as varbinary(1)) as int) & 1 = 1 THEN '1' ELSE '0' END)

    SET @valInPos = @valInPos + 1

    END

    RETURN(@strRet)

    END

    GO

    which you use like this

    SELECT dbo.HEXTOBIN(0x00280000)

    and handles the first step of getting it to 0's and 1'.

  • OK combine my HEXTOBIN with Matts BINTODEC functions and you get this as long as you are working with fixed start and stop positions.

    SELECT

    dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),29,4)) a,

    dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),16,13)) b,

    dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),11,5)) c,

    dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),5,5)) d

    And if you are coming from the decimal change

    0x0A0401E4

    to

    cast(168034788 as varbinary(100))

    and you should be good to go.

  • Sorry found a typo this is the corrected version 😀

    SELECT

    dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),28,5)) a,

    dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),15,13)) b,

    dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),10,5)) c,

    dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(0x0A0401E4),5,5)) d

  • Thanks for the replies...

    Yes the widths are fixed ie . always the first 5 from left, then 13 etc. etc.

    I'll play with some of the suggestions and see how far I get....other work getting in the way right now 🙂

  • You didn't answer my question, though... Do you expect the right most bit of each of those groups to represent 20 in your returned decimal number?

    --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.


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

  • The rightmost bit (if set) of each "group" would be 1...ie 8421

    ^

    Looks like between the 2 functions, I've got it working - I'll test with some more data - after I do it by hand 🙂

    Thanks again for all the help!!!

  • DECLARE@HEX BINARY(4)

    SET@HEX = 0x00280000

    SELECT(CAST(@HEX AS BIGINT) / POWER(2, 18)) & 0x1f


    N 56°04'39.16"
    E 12°55'05.25"

Viewing 15 posts - 1 through 15 (of 19 total)

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