Nik - 1 year ago 71

Android Question

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

Answer

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

Source (Stackoverflow)