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.
- A field can have a maximum of 180 consecutive internal joins. A field with excessive joins will be split into joined consecutive strings.
- &
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.
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.
