SQL Server Bitwise OR – Convert Integer Result to Table of Individual Integers

bitwise-comparisonsql serversql-server-2016

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).

Best Answer

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.

Related Question