Bitwise Operations in SQL

It is sometimes useful to perform operations on an integer field that is actually a bitmask containing sets of boolean values represented by the binary bits. For example an 8-byte integer value might contain 64 bits of information regarding a bank of switches that are either 1=on or 0=off. To determine the state of the switches, or to set their values, the SQL bitwise operators AND,OR and NOT are typically used. Another common bitwise operator XOR is not standard SQL but is provided as built-in function xor().

For example the following operations compare the bits in a database field initialized with the value 255 (binary 00000000000000000000000011111111) with the integer value 3 (binary 00000000000000000000000000000011):

 
    XSQL>create table x(a integer);
    XSQL>insert into x values (255);
    XSQL>select a and 3 from x;
    #1
    ------------------------------------------------------------------------------
    3
 
    Selected records: 1
     
    XSQL>select a or 3 from x;
    #1
    ------------------------------------------------------------------------------
    255
     
    Selected records: 1
     
    XSQL>select not a from x;
    #1
    ------------------------------------------------------------------------------
    -256
 
    Selected records: 1
 
    XSQL>select xor(a,3) from x;
    #1
    ------------------------------------------------------------------------------
    252
     
    Selected records: 1
     

Note the use of function xor() to obtain the value of “a XOR 3”. Also note that the result of the NOT operator is actually equivalent to the C language ~ operator and the result may be a negative number because eXtremeSQL stores intermediate values internally as 8-byte integer values. As explained at link: http://www.geeksforgeeks.org/interesting-facts-bitwise-operators-c/ care should be taken when using bitwise NOT as demonstrated with the following C program:

 
    // Note that the output of following program is compiler dependent
    int main()
    {
        unsigned int x = 1;
        printf("Signed Result %d \n", ~x);
        printf("Unsigned Result %ud \n", ~x);
        return 0;
    }
     
    Output:
            
    Signed Result -2
    Unsigned Result 4294967294d