Help Center > > Developer Guide> SQL Reference> Functions and Operators> Bit String Functions and Operators

Bit String Functions and Operators

Updated at: Jul 15, 2020 GMT+08:00

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. When bit shifting, the original length of the string is preserved by zero padding (if necessary).

  • ||

    Description: Connects bit strings.

    For example:

    1
    2
    3
    4
    5
    SELECT B'10001' || B'011' AS RESULT;
      result
    ----------
     10001011
    (1 row)
    
  • &

    Description: AND operation between bit strings

    For example:

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

    Description: OR operation between bit strings

    For example:

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

    Description: 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.

    For example:

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

    Description: NOT operation between bit strings

    For example:

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

    Description: binary left shift

    For example:
    1
    2
    3
    4
    5
    SELECT B'10001' << 3 AS RESULT;
     result  
    ----------
     01000
    (1 row)
    
  • >>

    Description: binary right shift

    For example:

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

The following SQL-standard functions work on bit strings as well as character 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. For example:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT 44::bit(10) AS RESULT;
   result
------------
 0000101100
(1 row)

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

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

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

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

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel