I have a table P that has a Categories column. The Categories column is a bitwise flag; I have a Categories Table and if a row from P belongs in 2 different categories(say with Id 1 and 3), its column Category(or IntValue) would be 5, a sum of the first and third bit. I also have a Exclusion table by Ids in P, for example, I can say that I want to exclude from a search on P with Id=1, every category of Id=2 I would like to select All P, excluding categories in the exclusion table. For the moment I have this condition : p.Categories & @SUM_OF_EVERY_CATEGORY_INT_VALUE - (SELECT COALESCE(SUM(IntValue), 0) FROM CategoryExceptionByUser LEFT JOIN PCategories ON CategoryExceptionByUser.PCategoryId = PCategories.PCategoryId WHERE UserId = @CURRENT_USER) > 0 Which works only if you add every category from a product in the exclusion table. I would like it to work if I add only one of the categories to the exclusion table I am sorry if it's not clear, english is not my first language. If you need any more detail, please ask away Here is the link for the example: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1d01677893041ecfd6054e63e5241bad The query on table P returns Ids 0, 2 and 4, I would like it to return Ids 1, 2 and 3

Can someone explain the purpose of using bitwise operators(like BIT_OR) in MySQL queries. For example, if have a table such as following: What is the purpose of aggregate operation like: SELECT name, value FROM table GROUP BY name HAVING BIT_OR(value) = 0;

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

We are in the process of creating a new application where various tables are used to hold enumerations. Since the IDs in these tables will be used in bitwise comparisons, we'd like to ensure new rows inserted will automatically use the decimal number that corresponds to the next bit being "set". For instance, if we had an alarm clock application, we might include a "Weekdays" table which could be used in the following way: CREATE TABLE dbo.Weekdays ( WeekdayID INT NOT NULL CONSTRAINT PK_Weekdays PRIMARY KEY CLUSTERED , WeekdayName VARCHAR(20) ); INSERT INTO dbo.Weekdays VALUES (1, 'Sunday') , (2, 'Monday') , (4, 'Tuesday') , (8, 'Wednesday') , (16, 'Thursday') , (32, 'Friday') , (64, 'Saturday'); CREATE TABLE dbo.AlarmsDefined ( AlarmID INT NOT NULL CONSTRAINT PK_AlarmsDefined PRIMARY KEY CLUSTERED , Weekdays INT NOT NULL ); INSERT INTO dbo.AlarmsDefined (AlarmID, Weekdays) VALUES (1, 50); SELECT W.WeekdayName FROM dbo.AlarmsDefined AD INNER JOIN dbo.Weekdays W ON (AD.Weekdays & W.WeekdayID) = W.WeekdayID The results of the above SELECT query: I'd like to be able to define the Weekdays table in the above example such that if we added another row to the table, the next WeekdayID would be automatically set as 128, which is the next decimal number that can be used in bitwise comparisons. This of course can be accomplished by putting in the values by hand into the primary key column. But is there a way to specify an automatically incrementing value which would adhere to the flag values? For example these would be the unique PK values generated: ID -- 1 2 4 8 16 32 ... and the trigger would automatically look at the last value and generate the next in the sequence needed?

Any idea of why this is so, CREATE TABLE f AS SELECT b'000000001' AS a, x'01' AS b; This creates two columns of a varbinary(2) NOT NULL, b varbinary(1) NOT NULL However, when I run, SELECT a=0, b=0, a=b, ab FROM f; +-----+-----+-----+------+ | a=0 | b=0 | a=b | ab | +-----+-----+-----+------+ | 1 | 1 | 0 | 1 | +-----+-----+-----+------+ What's going on here? From the [docs on *The BINARY and VARBINARY Types*,](https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html) > **All bytes are significant in comparisons, including ORDER BY and DISTINCT operations.** 0x00 bytes and spaces are different in comparisons, with 0x00 b, but why does a=0, or b=0 there?

I was running a trace on one of our test servers when someone did this: and one of the queries I could catch in the trace was: declare @UserOption int select @UserOption=convert(int, c.value) from sys.configurations c where c.name='user options' SELECT CAST(@UserOption & 1 AS bit) AS [DisableDefaultConstraintCheck], CAST(@UserOption & 2 AS bit) AS [ImplicitTransactions], CAST(@UserOption & 4 AS bit) AS [CursorCloseOnCommit], CAST(@UserOption & 8 AS bit) AS [AnsiWarnings], CAST(@UserOption & 16 AS bit) AS [AnsiPadding], CAST(@UserOption & 32 AS bit) AS [AnsiNulls], CAST(@UserOption & 64 AS bit) AS [AbortOnArithmeticErrors], CAST(@UserOption & 128 AS bit) AS [IgnoreArithmeticErrors], CAST(@UserOption & 256 AS bit) AS [QuotedIdentifier], CAST(@UserOption & 512 AS bit) AS [NoCount], CAST(@UserOption & 1024 AS bit) AS [AnsiNullDefaultOn], CAST(@UserOption & 2048 AS bit) AS [AnsiNullDefaultOff], CAST(@UserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull], CAST(@UserOption & 8192 AS bit) AS [NumericRoundAbort], CAST(@UserOption & 16384 AS bit) AS [AbortTransactionOnError] there is lots of information that can fit in a int if you know - How does the operator & work?

I'm designing a table to keep track of a value that has to represent the on-off setting of several hundred states. I would expand this to 512, to give room for future growth and be a power of two. So I want a 64 byte = 512 bit column. My problem is that SqlServer bitwise operations don't support right operands larger than int, so there would be no way to access any bits past 32. How can I handle this issue?

I have a role system setup that checks to see if bits are set for specific roles. Each user has a specific assigned that is stored in our database and gives them access to different parts of the program. What I'm trying to do is pull every user from the database and add a column beside the role number with what parts they have access to. I can do this with a case statement but that will only find one of the permissions unless I break down all the combinations of cases. ------------------ Database Setup: Name | Role | Jon | 77 | --------------- Permissions: @ONE = 1 @TWO = 2 @THREE = 4 @FOUR = 8 @FIVE = 16 @SIX = 32 @SEVEN = 64 @EIGHT = 128 @NINE = 256 @TEN = 1024 @ELEVEN = 2048 @TWELVE = 32768 @THIRTEEN = 65471 ----------------- So for Jon I want to see: Name | Role | Roles Jon | 77 | One Three Four Seven

How can I mix boolean logic with bitwise and/or operators in SQL Server, specifically SQL Azure Database and/or SQL Server 2016? To demonstrate what I'm trying to do, consider the following script: DECLARE @String varchar(2000) = 'asdf' DECLARE @Flag1 bit = 1 DECLARE @Flag2 bit = 0 DECLARE @Data Table ( Value bit ) INSERT INTO @Data VALUES (@Flag1 | @Flag2) --INSERT INTO @Data VALUES ((@Flag1 | @String LIKE '%qwerty%') & @Flag2) SELECT * FROM @Data The script works fine as-is but the moment you uncomment out that second INSERT, all hell breaks loose. I understand this is invalid syntax because I'm mixing boolean logic with bitwise operators. But what is the right way to get this logic in there? Without going crazy with CASE WHEN statements, is there some way to do this?

What is the best way to index a column for an efficient look-up based on a bitwise comparison in the WHERE clause e.g. SELECT ID, FLAGS, NAME FROM TABLE WHERE FLAGS & 4 = 4 I am in a SQL server 2012 environment. I understand that an index on the FLAGS column won't be used in this type of query. I know the other option is to break the FLAGS column out into individual columns, but I wanted to avoid that because in some cases, that would mean 25+ new columns, each of which would need a combination of indexes to support the common queries. Are there any ways to index the single column so that the bitwise queries are more efficient? I tried using a computed column with an index as suggested by dudu below, but when I used in on a table which has a clustered index, and start selecting columns (instead of just select count(*) it no longer uses the index on that new computed column. SELECT * INTO MYTABLE FROM ( SELECT 'A' COL_A, 'A' COL_B, CAST(129 AS INT) FLAGS UNION SELECT 'B' COL_A, 'D' COL_B, CAST(129 AS INT) FLAGS UNION SELECT 'C' COL_A, 'S' COL_B, CAST(129 AS INT) FLAGS UNION SELECT 'D' COL_A, 'F' COL_B, CAST(129 AS INT) FLAGS UNION SELECT 'E' COL_A, 'T' COL_B, CAST(128 AS INT) FLAGS UNION SELECT 'F' COL_A, 'D' COL_B, CAST(128 AS INT) FLAGS UNION SELECT 'G' COL_A, 'D' COL_B, CAST(128 AS INT) FLAGS UNION SELECT 'H' COL_A, 'X' COL_B, CAST(128 AS INT) FLAGS ) SRC CREATE CLUSTERED INDEX IX_CLU_A ON MYTABLE(COL_A) ALTER TABLE MYTABLE ADD FLAG_BIT_1 AS (CAST ((FLAGS & 1) AS BIT)) CREATE INDEX MYTABLE_IX_FLAG_BIT_1 ON MYTABLE (FLAG_BIT_1) SELECT * FROM MYTABLE WHERE FLAGS & 1 = 1 SELECT * FROM MYTABLE WHERE FLAG_BIT_1 = 1 SELECT COUNT(*) FROM MYTABLE WHERE FLAGS & 1 = 1 SELECT COUNT(*) FROM MYTABLE WHERE FLAG_BIT_1 = 1

I am trying to figure out all of the common bit-wise operations in Oracle. In SQL Server we have some very simple bit-wise operators to use against a bit-wise value: * & - Evaluates if bit exists select 10 & 2 /* result=2 */ * | - Add Bit (if doesn't exist) select 10 | 2 /* result=10 */ * &~ - Remove Bit (if exists) select 10 &~ 2 /* result=8 */ * ^ - Toggle Bit (remove if exists, adds if doesn't) select 10 ^ 2 /* result = 8 */ In Oracle, I know we have a built-in bitand() function which is more or less equivalent to SQL Server &, and I have built an Oracle bit-wise OR function to mimic SQL server's bit-wise OR operation (|) as follows: CREATE OR REPLACE FUNCTION BITOR (x IN NUMBER, y IN NUMBER) RETURN NUMBER AS BEGIN RETURN x + y - BITAND(x,y); END; **My question is**: how can I achieve the SQL Server &~ and the ^ operations in Oracle?

I have a query similar to this below. As you can see, there are multiple parts which are very similar. SELECT id, aaaa, bbbb, tags ,( SELECT COUNT(*) > 0 FROM A LEFT JOIN B ON A.id = B.parent_id WHERE A.buzz=C.buzz AND B.tags & 1 0 ) as "has_children_tag_1" ,( SELECT COUNT(*) > 0 FROM A LEFT JOIN B ON A.id = B.parent_id WHERE A.buzz=C.buzz AND B.tags & 2 0 ) as "has_children_tag_2" ,( SELECT COUNT(*) > 0 FROM A LEFT JOIN B ON A.id = B.parent_id WHERE A.buzz=C.buzz AND B.tags & 4 0 ) as "has_children_tag_3" ,( SELECT COUNT(*) > 0 FROM A LEFT JOIN B ON A.id = B.parent_id WHERE A.buzz=C.buzz AND B.tags & 8 0 ) as "has_children_tag_4" FROM A LEFT JOIN Z ON A.id = Z.id I know one thing, that I cannot split flag column into multiple bit columns, so I have to figure out how to query my existing tables in efficient way. I am wondering is it possible (I don't know how to do that) to create some kind of temporary table for part: SELECT COUNT(*) > 0 FROM A LEFT JOIN B ON A.id = B.parent_id WHERE A.buzz=C.buzz to have a possibility to run on this tmp table WHERE B.tags & 1 0, WHERE B.tags & 2 0, etc. Any help/suggestion will be appreciated.