I have a column CategoryId that will store more than one value at the time, some of them have 1 (BA), 2 (SA) or 3 (both). I'm not sure if this is the right way.
For example the query down brings all records because 3 includes 1 and 2. If I want rows that have both categories then bitwise does not work. I believe I'm confusing terms.
Sample data and query:
CREATE TABLE #Payment (Id INT, Name NVARCHAR(50), CategoryId INT)
INSERT #Payment (Id, Name, CategoryId) VALUES(1, N'A', 1) --BA
INSERT #Payment (Id, Name, CategoryId) VALUES(1, N'B', 2) --SA
INSERT #Payment (Id, Name, CategoryId) VALUES(1, N'C', 3) --BA and SA
INSERT #Payment (Id, Name, CategoryId) VALUES(1, N'D', 2) --SA
DECLARE @Value INT = 3
SELECT *
FROM #Payment
WHERE (CategoryId & @Value) = CategoryId
There is a subtle correction needed in the WHERE clause. It should be:
WHERE (CategoryID & 3) = 3 -- bits 1 and 2 are set (matches 3, 7, 11, 15, ...)
For completeness here are the other variations:
WHERE (CategoryID & 3) <> 0 -- bits 1 or 2 are set (matches 1, 2, 3, 5, 6, 7, 9, 10, 11, ...)
WHERE (CategoryID & 3) = 0 -- bits 1 and 2 are not set (matches 0, 4, 8, 12, ...)
WHERE (CategoryID & 3) = CategoryID -- bits other than 1 and 2 are not set (matches 0, 1, 2, 3)