How to select with bitwise flag values in SQL

Question

I have two tables in a SQL Server DB. One table BusinessOperations has various information about this business object, the other table OperationType is purely a bitwise flag table that looks like this:

| ID | Type    | BitFlag |
|  1 | Basic-A |   -2    |
|  2 | Basic   |   -1    |
|  3 | Type A  |   0001  |
|  4 | Type B  |   0002  |
|  5 | Type C  |   0004  |
|  6 | Type D  |   0008  |
|  7 | Type E  |   0016  |
|  8 | Type F  |   0032  |

The BitFlag column is a varchar column, the bitflags were inserted as '0001' as an example. In the BusinessOperations table, there's a column where the application that uses these tables updates it based on what is selected in the application's UI. As an example, I have one type which has the Basic,Type A, and Type B types selected. The column value in BusinessOperations is 3.

Based on this, I am trying to write a query which will show me something like this:

| ID | Name |  Description  |      OperationType    |
|  1 | Test |     Test      | Basic, Type A, Type B |

Here is the actual layout of the BusinessOperations table (Basic-A and Basic are bit columns:

| ID | Name |  Description  | Basic-A | Basic | OperationType |
|  1 | Test |     Test      |    0    |   1   |       3       |

There is nothing that relates these two tables to each other, so I cannot perform a join. I am very inexperienced with bitwise operations and am at a loss on how exactly to structure my select query which is being used to analyze this data. I feel like it needs a STUFF or CASE, but I don't know how I can get this to just show the types and not just the resultant BitFlag.

SELECT ID, Name, Description, OperationType
FROM OperationType
ORDER BY ID

Answer 1

Since you're storing the flag in OperationType as a VARCHAR, the first thing you need to do to is CONVERT or CAST the string to a number so we can do proper bitwise comparisons. I'm slightly unfamiliar with SQL Server, but you may need to remove the leading zeroes before the cast. Thus, the OperationType column in our desired SQL will look something like

CONVERT(INT, BitFlag)

Then, comparing that to our OperationType column would look something like

CONVERT(INT, BitFlag) & OperationType

The full query would look something like (forgive my lack of SQL Server expertise again):

SELECT bo.ID, bo.Name, bo.Description, ot.Type
FROM BusinessOperations AS bo
JOIN OperationType AS ot
ON CONVERT(INT, ot.BitFlag) & OperationType <> 0

The above query will effectively get you a list of the OperationTypes. If you absolutely need them on one line, see other answers to learn how to emulate something like GROUP_CONCAT in SQL Server. Disclaimer: Joining on a bitmask gives no guarantee of performance.

The other problem this answer does not solve is that of your legacy Basic and Basic-A fields. Personally, I'd do one of two things:

1. Remove them from the OperationType table and have the application tack the two on, based on the Basic and Basic-A columns as appropriate.
2. Put Basic and Basic-A as their own, positive flags in the OperationType table, and have the application populate the legacy columns as well as the OperationType column as appropriate.

As Aaron Bertrand has said in the comments, this really isn't an issue for Bitmasking at all. Having a many-many table that associates BusinessOperations.ID to OperationType.ID would solve all your problems in a much better way.