How to compose T-SQL WHERE statement to determine whether the specific filter value is a power of 2 collectible from column values

I got SQL Table and column called MultiTypes (int). MultiyTypes column values are regular numbers. I need to decompose those numbers to power-of-2 collectibles and then to check whether the filter value is one of these collectibles.

How to construct my WHERE Statement properly?

Example: Column has values:

10 which is (8+2), 
25 which is (16+8+1), 
17 which is (16+1), 
101 which is (64+32+4+1)

I want to build query

SELECT * FROM TABLE
WHERE @FilterValue -???-> MultiTypes

-???-> means – @FilterValue is one of the “power-of-2” collectibles of the iterated value

If filter value is 8, I will get rows with MultiTypes values 10 and 25 If filter value is 1, I will get rows with MultiTypes values 17, 101 and 25

Answer

If you want to know if a filter value matches a bit-array in the “1” positions, you use bitwise-and. It looks like:

where MultiType_Column & @filter = @filter

There is no need to decompose the value to specific types.

If you want to know if any of the values are set, then you can use:

where MultiType_Column & @filter <> 0