Mysql Bitwise operations and filter

Question

I try to implement a bitwise filter using MYSQL (with udf if needed)

The filter is something like a AND but I want to use the mask to build a new bit string... Let me explain you with a sample :

Suppose I have a table with blob storing 8 bit streams:

data1: 10110110
data2: 01100010
data3: 00010011

Then I have a mask to apply to get the bits from data when mask value is 1

MASK: 00101011

And so get the following expected results:

data1: 1010
data2: 1010
data3: 0011

Is there a way to optimize the filtering, without looping on each bit of "mask" to get the corresponding value in "data" row...

CLARIFICATION

I've just taken 8 bits for the post, but it's more like 256 bytes

for Joe : To clarify the exemple, the mask 00101011 is interpreted as : get the bit value from data field at position 3,5,7,8, if you read the mask from left to right, enumerated from bit 1 to bit 8... Hope this clarification is "clear"...

I'm not seeing how that mask and those rows is supposed to return that data — Joe
– Joe, Commented Oct 3, 2011 at 17:06
In my opinion it's really difficult to implement this without a loop. — Karolis
– Karolis, Commented Oct 4, 2011 at 10:24

Johan · Accepted Answer · 2011-10-03 16:57:26Z

9

You can use bitwise operators in MySQL:

http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html

Example:

SELECT (data1 & b'00101011') as output1 FROM ......

Quick test:

SELECT (b'10110110' & b'00101011') as output1

This does a bitwise AND with the binary pattern of the mask you specified.
See the above link for more toys.

answered Oct 3, 2011 at 16:57

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karolis Over a year ago

I don't think this is what he wants.

Ilmari Karonen · Accepted Answer · 2017-10-30 13:44:50Z

The only way I know of doing what you want is something like

SELECT ((data >> 2) & 8) | ((data >> 1) & 4) | (data & 3) FROM ...

Obviously, you'll have to construct the expression based on your mask; it's not very difficult to do, just a bit tedious — you basically need to loop over the bits in the mask, something like this:

var mask = 0b00101011;
var parts = new Array();
var shift = 0;
var unshift = 0;
while (mask > 0) {
    while ((mask & 1) == 0) {
        shift = shift + 1;
        mask = mask >> 1;
    }
    submask = 0;
    while ((mask & 1) == 1) {
        submask = submask + (1 << unshift);
        unshift = unshift + 1;
        mask = mask >> 1;
    }
    parts.push( "((data >> " + shift + ") & " + submask + ")" );
}
var expr = parts.join( " | " );
console.log(expr);

The example code above is in JavaScript, so you can run it as a snippet here and get:

((data >> 0) & 3) | ((data >> 1) & 4) | ((data >> 2) & 8)

logged to the console, but it should be pretty easy to port to other languages.

Collectives™ on Stack Overflow

Mysql Bitwise operations and filter

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related