Bitwise operation in SQL Server

Question

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

Answer 1

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)