Nik - 1 year ago 133
Android Question

# Bitwise XOR in sqlite - bitwise NOT not working as I expect

I try to update flag in sqlite database.

I need to remove bit from my flag. Current flag vallue is 11.

In Java I simple do:

``````newflag = flag  ^ 2;
//newflag = 9
``````

In SQL I try:

``````... SET flag = flag | ~2 ...
``````

Now flag in db = -1. Why?

I try this in SQLite maganer for Firefox.

``````SELECT 9 | 2
``````

returns 11, but

``````SELECT 11 | ~2
``````

returns -1

To answer your question (why does 11 | ~2 evaluate to -1), let's break it up. First the bitwise complement:

``````> SELECT ~2;
-3
``````

Using binary notation, this is what's happening:

`````` 2 : 00000000000000000000000000000010
~2 : 11111111111111111111111111111101
-3 : 11111111111111111111111111111101
``````

SQLite takes your 2, applies a bitwise complement, and interprets it as a signed integer. In SQLite, ~n (where n is an integer value) is mathematically equivalent to -(n)-1, because it flips the sign bit along with all the other bits. To understand this better, read up on Two's complement:

http://en.wikipedia.org/wiki/Two's_complement

Next the bitwise or:

``````> SELECT 11 | ~2;
-1
``````

Again in binary:

``````     11 : 00000000000000000000000000001011
~2 : 11111111111111111111111111111101
11 | ~2 : 11111111111111111111111111111111
-1 : 11111111111111111111111111111111
``````

As for emulating your Java: your Java uses the bitwise XOR operator (^). To emulate a bitwise XOR in sqlite, you can use this general form:

``````(~(a&b))&(a|b)
``````

http://www.mail-archive.com/sqlite-users@sqlite.org/msg02250.html

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download