Search code examples
mysqlsqlbitoperation

SQL Bit operation, change second last bit to 0

i have a lot of rows with an bigint(length 20) and i have to change every second last bit to 0. For example i have: 0101 1011 and the result i need is: 0101 1001 The Problem is, the numbers are "random" and i can't calcuate -2(10).

Solution

  • You can use the bitwise AND operator (&), ANDing with NOT 2 (created using the bitwise inversion operator (~)) to get your results e.g.

    CREATE TABLE test (num BIGINT(20));
INSERT INTO test VALUES
(4),
(91),
(9223372036854775807);
SELECT num, num & ~2 FROM test;

    Output:

    num                     num & ~2
4                       4
91                      89
9223372036854775807     9223372036854775805

    Demo on dbfiddle