Bitwise Functions
Bitwise functions operate from right to left, with shorter operands padded with binary zeroes to the left. Each result is a byte field the size of the longer operand, except BIT_NOT, which takes a single byte operand and returns the same-sized operand.
The external bitwise functions are:
BIT_ADD
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, the result of adding 200 and 90 would be 290, but 290 does not fit in one byte (maximum value is 255), so the overflow is disregarded and the logical add result is 34 (290 minus 256).
BIT_AND
Returns the logical "and" of two byte operands. If two bits are 1, the answer is 1; otherwise the answer is 0.
BIT_NOT
Returns the logical "not" of a single byte operand.
BIT_OR
Returns the logical "or" of two byte operands. If either or both bits are 1, the answer is 1.
BIT_XOR
Returns the logical "xor" of two byte operands. If either, but not both, bits is 1, the answer is 1; otherwise the answer is 0.
INTEXTRACT(byte, n)
Similar to the CHAREXTRACT string function. Returns the nth byte as an integer. For example: intextract(X'0A020C04', 3) returns 12, which is the integer value for the third byte '0C'.
n is an integer field. If n is less than 1 or larger than the number of bytes available, then a 0 is returned. For example: 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.