Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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).
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.
SELECT CAST(1 AS INT) AS RowNumber, CAST(0 AS INT) AS BitValue) is completely superfluous, and secondly, starting with the row number of 1 and capping the output at the row number of 30 limits the max value to just 268,435,456. This way there's also no correlation between RowNumber and BitValue, though I can see that the solution doesn't require it.
– Andriy M
Nov 27 '19 at 7:22
(0, 1) and ended with (30, 1073741824). You could do it without the POWER function too. One other peculiarity is your usage of CROSS JOIN. I'm not sure if it makes it better in any way, I'm just curious why you decided to go with using the parameter via a cross join instead of using it directly in the WHERE clause, i.e. b.BitValue & @Mask > 0.
– Andriy M
Nov 27 '19 at 7:23
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
