Bitwise operation for WHERE clause
-
I have a table with a bitmask value column and would like to select from this table where records have any match with another bitmask value. For instance 3 database records: ID, bitmask_value 1, 1 2, 3 3, 16 To check if a single bit is selected, I can: SELECT * FROM table WHERE (bitmask_value & 1) = 1 (checks if bit 1 is selected - returns ID 1 and ID 2) But I would like to get all records where a criteria bitmask value I define is selected, such as, user selects 1 and 2 = 3. I could do this: SELECT * FROM table WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2) BUT if my criteria bitmask value includes several bits, can I query a different way to compare bitmask_value and criteria_bitmask, and get the proper results without checking each bit individually? So given a criteria_bitmask value of 3... can I query using the value 3 somehow and get the following: -any rows with 1 selected -any rows with 2 selected -any rows with 1 and 2 selected -any rows with 1 and any others selected -any rows with 2 and any others selected -any rows with 1 and 2 and any others selected I'm only filtering rows that DO NOT have a 1 or a 2 or both a 1 and a 2.
-
I have a table with a bitmask value column and would like to select from this table where records have any match with another bitmask value. For instance 3 database records: ID, bitmask_value 1, 1 2, 3 3, 16 To check if a single bit is selected, I can: SELECT * FROM table WHERE (bitmask_value & 1) = 1 (checks if bit 1 is selected - returns ID 1 and ID 2) But I would like to get all records where a criteria bitmask value I define is selected, such as, user selects 1 and 2 = 3. I could do this: SELECT * FROM table WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2) BUT if my criteria bitmask value includes several bits, can I query a different way to compare bitmask_value and criteria_bitmask, and get the proper results without checking each bit individually? So given a criteria_bitmask value of 3... can I query using the value 3 somehow and get the following: -any rows with 1 selected -any rows with 2 selected -any rows with 1 and 2 selected -any rows with 1 and any others selected -any rows with 2 and any others selected -any rows with 1 and 2 and any others selected I'm only filtering rows that DO NOT have a 1 or a 2 or both a 1 and a 2.
Sorry, some of your terminology is confusing me.
Leftyfarrell wrote:
1 and 2 = 3.
Actually 1 AND 2 = 0. Did you mean 1 OR 2?
Leftyfarrell wrote:
SELECT * FROM table WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2)
What about:
WHERE (bitmask_value & 3) <> 0bitmask_value result
0 0
1 1
2 2
3 3
4 0
5 1
6 2
7 3
8 0
etc.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
Sorry, some of your terminology is confusing me.
Leftyfarrell wrote:
1 and 2 = 3.
Actually 1 AND 2 = 0. Did you mean 1 OR 2?
Leftyfarrell wrote:
SELECT * FROM table WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2)
What about:
WHERE (bitmask_value & 3) <> 0bitmask_value result
0 0
1 1
2 2
3 3
4 0
5 1
6 2
7 3
8 0
etc.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
I did mean 1 OR 2 selected. By saying 1 and 2 = 3, I meant that the search criteria is defined for me by a user selecting options, and if they select options 1 and 2, I need to pull anyone that matches 1 OR 2. Their interface assumes an OR condition on the options they select. I believe what you have provided is what I need. I just need to test a bit more. Thank-you.