stack - 2 years ago 132
SQL Question

# How to update a single bit of a binary number as toggle?

I have this table:

``````// numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(10)  |
+---------+------------+
| 1       | 1001100111 |
| 2       | 0111000101 |
| 3       | 0001101010 |
| 4       | 1111111011 |
+---------+------------+
``````

I'm trying to change (toggle) the value of seventh digit (right to left). So focus on this digit:

``````// for example
1001100111
^ seventh digit (rtl)
``````

And this is expected result:

``````// new_numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(10)  |
+---------+------------+
| 1       | 1011100111 |
| 2       | 0101000101 |
| 3       | 0011101010 |
| 4       | 1101111011 |
+---------+------------+
``````

I can update seventh digit like

``````// set it to zero
UPDATE numbers SET numb = numb & b'1110111111';
``````

But actually I need to toggle it.

• if it is
`0`
then set it
`1`

• if it is
`1`
then set it
`0`

How can I do that?

Flipping a bit is done by XOR `^` operation with a mask that has `1`s in positions that you would like to toggle.

This works because the truth table of XOR looks like this:

``````  A: 0 0 1 1
B: 0 1 0 1
A^B: 0 1 1 0
``````

Note the columns where `B` is `1`: when the corresponding bit in `A` is zero, the result is one, and vice verse.

Togging seventh bit is done like this:

``````UPDATE numbers SET numb = numb ^ b'0001000000';
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download