stack stack - 7 months ago 12
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?

Answer

Flipping a bit is done by XOR ^ operation with a mask that has 1s 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';