Bitwise Functions
Bitwise functions operate from right to left, with shorter operands padded with binary zeroes to the left.
The bitwise functions are:
BIT_ADD
BIT_ADD(expr, expr)
Operand type: BYTE
Result type: Same as operands
Returns the logical ADD of two byte operands; any overflow is disregarded.
"Logical add" is adding the binary content of the two operands as if they were unsigned binary integers. If the sum does not fit in the result field, the overflow is disregarded and the rest is retained.
For example: C8 + 5A = 0122.
SELECT HEX(BIT_ADD(BYTE(x'C8'), BYTE(x'5A')))
Result is hex 22 because the 01 is dropped as part of the carry over.
BIT_AND
BIT_AND(expr, expr)
Operand type: BYTE, or IPV4 or IPV6 address
Result type: Same as operands
Returns the logical AND of the two operands.
If two bits are 1, the answer is 1; otherwise the answer is 0.
SELECT BIT_AND(IPV4('255.255.255.0'),IPV4('172.16.254.1'))
returns (as an IPv4)
172.16.254.0
BIT_NOT
BIT_AND(expr)
Operand type: Single BYTE, or IPV4 or IPV6 address
Result type: Same as operand
Returns the logical NOT of the operand.
SELECT BIT_NOT(IPV4('172.16.254.1'))
returns (as an IPv4):
83.239.1.254
BIT_OR
BIT_OR(expr, expr)
Operand type: BYTE, or IPV4 or IPV6 address
Result type: Same as operands
Returns the logical OR of the two operands.
If either or both bits are 1, the answer is 1.
SELECT BIT_OR(IPV4('255.255.255.0'),ipv4('172.16.254.1'))\g
returns (as an IPv4):
255.255.255.1
BIT_XOR
BIT_XOR(expr, expr)
Operand type: BYTE, or IPV4 or IPV6 address
Result type: Same as operands
Returns the logical XOR of the two operands.
If either, but not both, bits is 1, the answer is 1; otherwise the answer is 0.
SELECT BIT_XOR(IPV4('255.255.255.0'),IPV4('172.16.254.1'))
returns (as an IPv4)
83.239.1.1
INTEXTRACT(byte, n)
Operand types: BYTE, INTEGER
Result type: INTEGER
Returns the nth byte as an integer. n is an integer field. Similar to the CHAREXTRACT string function.
INTEXTRACT(X'0A020C04', 3)
returns 12, which is the integer value for the third byte '0C'.
If n is less than 1 or larger than the number of bytes available, then a 0 is returned.
INTEXTRACT(X'0A020C04', -2)
or
INTEXTRACT(X'0A020C04', 5)
both return 0.
HASH
HASH(expr)
Operand type: Any except long types
Result type: INTEGER4
Generates a four-byte numeric value from expressions of all data types except long types. The implicit size for the expression can affect the result.
SELECT HASH(1), HASH(int1(1)), HASH(int2(1)), HASH(int4(1))\g
returns the following single row:
Col1 Col2 Col3 Col4
--------------------------------------------------
-1489118143 1526341860 ‑1489118143 1711753094
Note: Because the constant 1 is implicitly a short integer, only the return values for HASH(1) and HASH(int2(1)) match. For the remaining columns, the difference in the number of bytes holding the integer leads to a different hash value. Also, the generated hash value is not guaranteed unique, even if the input values are unique.