Updated on 2024-08-20 GMT+08:00

Bit String Functions and Operators

Bit String Operators

Aside from the usual comparison operators, the following operators can be used. Bit string operands of &, |, and # must be of equal length. In case of bit shifting, the original length of the string is preserved by zero padding (if necessary).

  • ||

    Description: Connects bit strings.

    Example:

    1
    2
    3
    4
    5
    gaussdb=# SELECT B'10001' || B'011' AS RESULT;
      result
    ----------
     10001011
    (1 row)
    
    • A field can have a maximum of 180 consecutive internal joins. A field with excessive joins will be split into joined consecutive strings.

      Example: str1||str2||str3||str4 is split into (str1||str2)||(str3||str4).

    • In ORA-compatible mode, if bit strings contain a null string, the null string is ignored and other strings are joined. In other compatibility modes, the null string is returned.

      Take str1||NULL||str2 as an example. str1str2 is returned in ORA-compatible mode and NULL is returned in other compatibility modes.

  • &

    Description: Specifies the AND operation between bit strings.

    Example:

    1
    2
    3
    4
    5
    gaussdb=# SELECT B'10001' & B'01101' AS RESULT;
     result 
    --------
     00001
    (1 row)
    
  • |

    Description: Specifies the OR operation between bit strings.

    Example:

    1
    2
    3
    4
    5
    gaussdb=# SELECT B'10001' | B'01101' AS RESULT;
     result 
    --------
     11101
    (1 row)
    
  • #

    Description: Specifies the OR operation between bit strings if they are inconsistent. If the same positions in the two bit strings are both 1 or 0, the position returns 0.

    Example:

    1
    2
    3
    4
    5
    gaussdb=# SELECT B'10001' # B'01101' AS RESULT;
     result 
    --------
     11100
    (1 row)
    
  • ~

    Description: Specifies the NOT operation between bit strings.

    Example:

    1
    2
    3
    4
    5
    gaussdb=# SELECT ~B'10001'AS RESULT;
     result  
    ----------
     01110
    (1 row)
    
  • <<

    Description: Shifts left in a bit string.

    Example:
    1
    2
    3
    4
    5
    gaussdb=# SELECT B'10001' << 3 AS RESULT;
     result  
    ----------
     01000
    (1 row)
    
  • >>

    Description: Shifts right in a bit string.

    Example:

    1
    2
    3
    4
    5
    gaussdb=# SELECT B'10001' >> 2 AS RESULT;
     result  
    ----------
     00100
    (1 row)
    

The following SQL-standard functions work on bit strings as well as strings: length, bit_length, octet_length, position, substring, and overlay.

The following functions work on bit strings as well as binary strings: get_bit and set_bit. When working with a bit string, these functions number the first (leftmost) bit of the string as bit 0.

In addition, it is possible to convert between integral values and type bit. Example:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
gaussdb=# SELECT 44::bit(10) AS RESULT;
   result
------------
 0000101100
(1 row)

gaussdb=# SELECT 44::bit(3) AS RESULT;
 result 
--------
 100
(1 row)

gaussdb=# SELECT cast(-44 as bit(12)) AS RESULT;
    result    
--------------
 111111010100
(1 row)

gaussdb=# SELECT '1110'::bit(4)::integer AS RESULT;
 result 
--------
     14
(1 row)

gaussdb=# SELECT substring('10101111'::bit(8), 2);
 substring
-----------
 0101111
(1 row)

Casting to just "bit" means casting to bit(1), and so will deliver only the least significant bit of the integer.