Use functions and operators to examine and manipulate bit strings - PolarDB - Alibaba Cloud - PolarDB

PolarDB for Oracle provides operators and functions for examining and manipulating bit strings — values of type bit and bit varying. While only type bit is mentioned in these tables, values of type bit varying can be used interchangeably.

Bit string operators

Operator	Description	Example	Result
`bit` `\|\|` `bit` → `bit`	Concatenation	`B'10001' \|\| B'011'`	`10001011`
`bit` `&` `bit` → `bit`	Bitwise AND. Inputs must be of equal length.	`B'10001' & B'01101'`	`00001`
`bit` `\|` `bit` → `bit`	Bitwise OR. Inputs must be of equal length.	`B'10001' \| B'01101'`	`11101`
`bit` `#` `bit` → `bit`	Bitwise exclusive OR. Inputs must be of equal length.	`B'10001' # B'01101'`	`11100`
`~` `bit` → `bit`	Bitwise NOT	`~ B'10001'`	`01110`
`bit` `<<` `integer` → `bit`	Bitwise shift left. String length is preserved.	`B'10001' << 3`	`01000`
`bit` `>>` `integer` → `bit`	Bitwise shift right. String length is preserved.	`B'10001' >> 2`	`00100`

Bit string functions

Some functions available for binary strings are also available for bit strings.

Function	Description	Example	Result
`bit_length(` `bits` `bit` `)` → `integer`	Returns the number of bits in the bit string.	`bit_length(B'10111')`	`5`
`length(` `bits` `bit` `)` → `integer`	Returns the number of bits in the bit string.	`length(B'10111')`	`5`
`octet_length(` `bits` `bit` `)` → `integer`	Returns the number of bytes in the bit string.	`octet_length(B'1011111011')`	`2`
`overlay(` `bits` `bit` `PLACING` `newsubstring` `bit` `FROM` `start` `integer` `[FOR` `count` `integer]` `)` → `bit`	Replaces the substring of `bits` starting at the `start`'th bit and extending for `count` bits with `newsubstring`. If `count` is omitted, it defaults to the length of `newsubstring`.	`overlay(B'01010101010101010' placing B'11111' from 2 for 3)`	`0111110101010101010`
`position(` `substring` `bit` `IN` `bits` `bit` `)` → `integer`	Returns the first starting index of `substring` within `bits`, or zero if not present.	`position(B'010' in B'000001101011')`	`8`
`substring(` `bits` `bit` `[FROM` `start` `integer]` `[FOR` `count` `integer]` `)` → `bit`	Extracts the substring of `bits` starting at the `start`'th bit, stopping after `count` bits. Provide at least one of `start` and `count`.	`substring(B'110010111111' from 3 for 2)`	`00`
`get_bit(` `bits` `bit`, `n` `integer` `)` → `integer`	Extracts the `n`'th bit from the bit string. The first (leftmost) bit is bit 0.	`get_bit(B'101010101010101010', 6)`	`1`
`set_bit(` `bits` `bit`, `n` `integer`, `newvalue` `integer` `)` → `bit`	Sets the `n`'th bit in the bit string to `newvalue`. The first (leftmost) bit is bit 0.	`set_bit(B'101010101010101010', 6, 0)`	`101010001010101010`

Casting between integers and bit strings

Cast integral values to and from type bit using the following rules:

Casting an integer to bit(n) copies the rightmost n bits.
Casting an integer to a bit string wider than the integer sign-extends on the left.
Casting to bit (without a length) is equivalent to casting to bit(1), which delivers only the least significant bit.

44::bit(10)               0000101100
44::bit(3)                100
cast(-44 as bit(12))      111111010100
'1110'::bit(4)::integer   14