I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries based on these fields using AND or OR logic.
Using a calculated field, where I just convert the bits into an integer value, I can easily find rows that match an AND query, by using a simple WHERE rowvalue = requestvalue, but the OR logic would require using bitwise & in order to find matching records.
Given that I would need to work with several of these columns and select from hundreds of millions of records, I feel that there would be a huge performance hit when doing bitwise & operations to filter my SELECT results.
I came across this answer from searching and it looked like it may fit my needs, but I need some clarification on how it is implemented.
Is this as simple as creating a lookup table that has all possible search conditions?
Example for 3 bits using (a & b) (Edit: Wrong bitwise op)
001,001 001,011 001,101 001,111 010,010 010,011 010,110 011,011 011,111 etc
The author mentions that it's counter-intuitive initially, but I can't help but feel I'm interpreting the solution incorrectly, as this would give me a single lookup table with likely billions of rows.
Any clarifications on the answer I linked above or other suggestions that would preserve the existing database are appreciated.
Edit: A more concrete example using small data.
Four flags, HasHouse,HasCar,HasCat,HasDog, 0000 is has none, 1111 is has all.
Any number of flags, from all to none, can be flipped, and results must be filtered where selection matches all (Using exact value comparison) or at least 1 (Using bitwise &).
Adding a single calculated column for each bitmask is ok, but adding a column for each bit for more than 100 bits, coupled with how to insert/update the data is why I'm trying to find alternative solutions.