BIT_AND
Takes the bitwise AND of all non-null input values. If the input parameter is NULL, the return value is also NULL.
Behavior Type
Syntax
BIT_AND (expression)
Parameters
| expression |
The BINARY or VARBINARY input value to evaluate. BIT_AND operates on VARBINARY types explicitly and on BINARY types implicitly through casts. |
Returns
BIT_AND returns:
- The same value as the argument data type.
- 1 for each bit compared, if all bits are 1; otherwise 0.
If the columns are different lengths, the return values are treated as though they are all equal in length and are right-extended with zero bytes. For example, given a group containing hex values ff, null, and f, BIT_AND ignores the null value and extends the value f to f0.
Examples
The example that follows uses table t with a single column of VARBINARY data type:
=> CREATE TABLE t ( c VARBINARY(2) );
=> INSERT INTO t values(HEX_TO_BINARY('0xFF00'));
=> INSERT INTO t values(HEX_TO_BINARY('0xFFFF'));
=> INSERT INTO t values(HEX_TO_BINARY('0xF00F'));
Query table t to see column c output:
=> SELECT TO_HEX(c) FROM t; TO_HEX -------- ff00 ffff f00f (3 rows)
Query table t to get the AND value for column c:
=> SELECT TO_HEX(BIT_AND(c)) FROM t; TO_HEX -------- f000 (1 row)
The function is applied pairwise to all values in the group, resulting in f000, which is determined as follows:
ff00(record 1) is compared withffff(record 2), which results inff00.- The result from the previous comparison is compared with
f00f(record 3), which results inf000.