Help with extracting Bits out of Binary Number

Question

Help with extracting Bits out of Binary Number

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

jim (11/6/2007)
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!!!

Heh... sounds a little backwards numerically speaking (0 power of most numbering systems is usually on the right-most digit), but if you've got it working, that's ok...

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

Chris Campbell-415954 SSCrazy Points: 2248 More actions · Answer 2

I love this topic...you don't get to do bitwise operations all that often.

I did this with the bitwise AND operator ("&") and a bitmask to find the values of the desired bits. Here's my example:

declare @x as int

declare @mask as int

declare @result as int

-- Use this to mask off the bits you are interested in.

-- "ANDing" this value with the source value will result in a value

-- with only the bits set that you're interested in.

-- The mask below equates to "0000 0000 0111 1100 0000 0000 0000 0000"

set @mask = 0x007C0000

-- This is a sample value with the bits you are interested in set plus

-- some other random ones you don't care about.

set @x = 0x00280FA1

print 'Starting value "x" : '

print @x

print ''

print 'Bit mask value : '

print @mask

-- This statement "ANDs" the starting value with the bitmask

-- using the SQL Bitwise AND operator "&"

-- The result will contain bits that are set only if they are set

-- in both operands of the expression.

set @result = @mask & @x

print ''

print 'AND Result: '

print @result

-- You're only intersted in bits 19 through 23 (assuming the rightmost bit is bit "1")

-- So you can "shift" the result by dividing by powers of 2...262144 is 2^18.

print ''

print 'Shift right 18 bits : '

print @result / 262144

Most of the code above is just displays and comments. Here's the short version:

declare @x as int

declare @mask as int

declare @result as int

set @mask = 0x007C0000

set @x = 0x00280FA1

set @result = (@mask & @x) / 262144

and you're done.

jim-540934 SSC Journeyman Points: 92 More actions · Answer 3

Chris -

This was actually the path I had started down and already had the proper masks in place...just wasn't to sure about the shifting part. I had looked into it a little....I'm pretty new to sql procedures etc. but managed to move forward with the previous functions. Still have a ways to go but am over the "hump"...Thanks to all - glad this was an interesting topic for you 🙂

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Sorry found I typoed my positions as I started with 4 and not 5 as the first set so here is the correction. @x represents your HEX value.

SELECT

dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(@x),28,5)) a,

dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(@x),15,13)) b,

dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(@x),10,5)) c,

dbo.BINTODEC(SUBSTRING(dbo.HEXTOBIN(@x),5,5)) d

But I had a chance to sit down and do what Chris was trying to point out and here it is

SELECT

(0x1F & @x) / 0x01 a,

(0x03FFE0 & @x) / 0x20 b,

(0x7C0000 & @x) / 0x040000 c,

(0x0F800000 & @x) / 0x800000 d

Based on the work Chris (although harder to manipulate) is the better choice performance wise, especially as the size increases but the only issue is developing the mask.

Using what we have already done this will help alleviate that some and plug mask and mask 2 in like this ( Ex. (mask & @x) / mask2 )

DECLARE @TotalBitLength INT,

@Start INT,

@Length INT

SET @TotalBitLength = 32

SET @Start = 10

SET @Length = 5

SELECT

CAST(dbo.BINTODEC(REPLICATE('0',@Start - 1) + REPLICATE('1',@Length) + REPLICATE('0',@TotalBitLength - @Start - (@Length - 1))) as VARBINARY(10)) Mask,

CAST(dbo.BINTODEC(REPLICATE('0',@Length - 1) + REPLICATE('1',1) + REPLICATE('0',@TotalBitLength - @Start - (@Length - 1))) as VARBINARY(10)) Mask2

Note the output will have extra 0's in the hex so like 0x1F may come out 0x0000001F, but this will not affect the outcome based on testing.

I know I learned a lot. 😎

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Sorry was using @x declared as int, seems to cause an issue when not int so if you use this with HEX in @x pos

SELECT

(0x1F & @x) / 0x01 a,

(0x03FFE0 & @x) / 0x20 b,

(0x7C0000 & @x) / 0x040000 c,

(0x0F800000 & @x) / 0x800000 d

you need to cast as an int if you get an error.