BIT_XOR

Declaration

BIT_XOR(expr) OVER (analytic_clause)

Description

Returns the bitwise XOR of all bits in expr.

The result type depends on whether the function parameters are evaluated as binary strings or numbers. If the parameter value is a binary string and is not a hexadecimal literal, bit literal, or NULL literal, binary string evaluation occurs. Otherwise, numeric calculation occurs, and the parameter value is converted to an unsigned 64-bit integer when necessary.

If no rows match, BIT_XOR() returns a neutral value with the same length as the parameter value (all bits set to 0). Unless all values are NULL, NULL values do not affect the result. In this case, the result is a neutral value with the same length as the parameter value.

Examples

CREATE TABLE tbl1 (year YEAR (4), month INT(2)UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);
INSERT INTO tbl1 VALUES(2021,1,1),(2021,1,22),(2021,1,3),(2021,2,2), (2021,2,23),(2021,2,23);

SELECT * FROM tbl1;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2021 |    01 |   01 |
| 2021 |    01 |   22 |
| 2021 |    01 |   03 |
| 2021 |    02 |   02 |
| 2021 |    02 |   23 |
| 2021 |    02 |   23 |
+------+-------+------+
6 rows in set (0.002 sec)

SELECT year,month,BIT_XOR(day) OVER (PARTITION BY year, month) AS days FROM tbl1;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2021 |    01 |   20 |
| 2021 |    01 |   20 |
| 2021 |    01 |   20 |
| 2021 |    02 |    2 |
| 2021 |    02 |    2 |
| 2021 |    02 |    2 |
+------+-------+------+
6 rows in set (0.002 sec)