• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. Functions and Operators
  6. Bit String Functions and Operators

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

  • ||

    Description: Connects bit strings.

    For example:

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

    Description: AND operation between bit strings

    For example:

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

    Description: OR operation between bit strings

    For example:

    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:

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

    Description: NOT operation between bit strings

    For example:

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

    Description: Shifts right in a bit string.

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

    Description: NOT operation between bit strings

    For example:

    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. For example:
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)
NOTE:

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