Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [bitwise-comparison]

In SQL Server, the following bitwise operators can be used:

  • & (bitwise AND)
  • | (bitwise OR)
  • ^ (bitwise exclusive OR)

MySQL provides the following bitwise operators:

  • & (Bitwise AND)
  • ~ (Invert bits)
  • | (Bitwise OR)
  • ^ (Bitwise XOR)
  • << (Left shift)
  • >> (Right shift)
12 questions
7
votes
1 answer

Best Way to Index an INT Column Used for BitWise Queries

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…
GWR
  • 2,847
  • 9
  • 35
  • 42
6
votes
1 answer

How does the operator "&" work in sql server?

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…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
5
votes
2 answers

MySQL when a = 0, b = 0, but a <> b (binary)

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, a<>b FROM…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
2
votes
2 answers

Column To generate Flag Values for use in bitwise comparisons

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…
ΩmegaMan
  • 409
  • 1
  • 8
  • 23
1
vote
2 answers

How to convert an integer that was the end result of a bitwise OR to a table of the individual integers that the bitwise OR was applied against?

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…
J.D.
  • 40,776
  • 12
  • 62
  • 141
1
vote
1 answer

Using a 512 bit binary column as a bit field

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…
Joshua Frank
  • 111
  • 2
1
vote
1 answer

Select Statement with Temp Column driven by Bitmask

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…
Zach Buechler
  • 13
  • 2
1
vote
1 answer

Boolean logic with BITs and Booleans in SQL Server

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…
Jaxidian
  • 472
  • 1
  • 5
  • 18
1
vote
1 answer

Oracle Equivalent of SQL Server's Bitwise Operators

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…
GWR
  • 2,847
  • 9
  • 35
  • 42
0
votes
1 answer

Exclude certain categories from a flag column

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…
shin0bi
  • 1
  • 1
0
votes
1 answer

Understanding the use of bitwise operators in MySQL?

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…
oneCoderToRuleThem
  • 1
0
votes
1 answer

Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

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 =…
noisy
  • 109
  • 1