How to convert an integer that was the end result of a bitwise OR to a table of the individual integers that the bitwise OR was applied against?

Question

How can I convert the final integer of a bitwise OR back to it's original set of integers that the bitwise OR operation was applied to?

For example if I have the following set of bit values: {0, 1, 2, 4, 8, 16} and if I have the bitwise OR generated value of 11 then I would like to convert that value of 11 back to 1, 2, and 8 (the only possible combinations of values).

Answer 1

The answer provided by J.D. works, except in the example the value 1 is not included in the results, even though for 11 the correct bits are 1, 2, 8.

The below example extends the logic from that answer, but uses a recursive CTE instead of a loop to generate the bit values and wraps it in a function that can be called easily to return a table of bits for a given mask. It also doesn't use a table to store the bit values and generates them on the fly.

Code:

CREATE FUNCTION fnGetBitsForMask (@Mask INT)
RETURNS TABLE
AS
RETURN
    WITH BitwiseValues AS
    (
        SELECT CAST(1 AS INT) AS RowNumber, CAST(0 AS INT)  AS BitValue
        UNION ALL
        SELECT b1.RowNumber+1 AS RowNumber, POWER(2, b1.RowNumber-1) AS BitValue
        FROM BitwiseValues b1
        WHERE b1.RowNumber < 30
    )
    SELECT b.BitValue
    FROM BitwiseValues b
    CROSS JOIN (SELECT @Mask AS Mask) m
    WHERE b.BitValue & m.Mask > 0

In use:

SELECT * FROM dbo.fnGetBitsForMask(11)

Result:

BitValue
1
2
8

As a Table-Valued Function, you can use this in SELECT statements and other queries without having to maintain tables or use loops.

Answer 2

I believe I figured out the answer. The quick version is, to convert from a bitwise OR generated value, I would need to apply a bitwise AND to between each individual bit value and the bitwise OR generated value.

The way I did this was I generated a table of all possible bitwise values in my set (I actually just made a table with the first 30 bitwise values using the SQL POWER() function, because after 30 you run into arithmetic overflow) and then cross joined that table to my bitwise OR generated value and selected the bitwise AND operation between the bitwise values and my bitwise OR generated value, then filtered out where the result is 0.

SQL to create bitwise values table:

DECLARE @LoopCounter AS INT = 0

WHILE (@LoopCounter < 30)
BEGIN

    SET @LoopCounter = @LoopCounter + 1

    INSERT INTO BitmaskValues
    SELECT @LoopCounter AS RowNumber, POWER(2, @LoopCounter) AS BitValue

END

SQL to join to my bitwise OR generated value and get my results after:

SELECT 11 AS BValue
INTO #Mask;

WITH BitwiseOperation AS
(
    SELECT BV.BitValue, M.BValue, BV.BitValue & M.BValue AS BitwiseAnd
    FROM BitmaskValue AS BV
    CROSS JOIN #Mask AS M
)

SELECT BitValue, BValue, BitwiseAnd
FROM BitwiseOperation
WHERE BitwiseAnd <> 0