BIT_
On this page
Performs an XOR function on corresponding pairs of bits (i.
Since BIT_ is an aggregate function, it can be used with the GROUP BY clause.
May be used as a window function.
Syntax
Aggregate Function
BIT_XOR (<expr1>, <expr2>)
Window Function
BIT_XOR(col | expr) OVER (
[PARTITION BY (col | expr), ...]
[ORDER BY (col | expr), ...]Arguments
-
expression: expressions must be integer data types.
Return Type
This will result in a one if two of the corresponding bits are different.
Examples
Aggregate Function
CREATE TABLE ba_numeric_sample (group_name varchar(10), ltr_val int);INSERT INTO ba_numeric_sample VALUES('alpha', 111), ('beta', 110), ('gamma', 000), ('delta', 001);
SELECT group_name, BIT_XOR(001) FROM ba_numeric_sample GROUP BY group_name;
+-------------------------+
|group_name | BIT_AND(001)|
+-------------------------+
|alpha | 1 |
+-----------|-------------+
|beta | 1 |
+-----------|-------------+
|delta | 1 |
+-----------|-------------+
|gamma | 1 |
+-----------+-------------+Window Function
CREATE TABLE player_scores(player_name VARCHAR(50), player_id VARCHAR(10),1st_qtr_score DECIMAL(5,2), 2nd_qtr_score DECIMAL(5,2),3rd_qtr_score DECIMAL(5,2), 4th_qtr_score DECIMAL(5,2),yearly_total AS 1st_qtr_score + 2nd_qtr_score + 3rd_qtr_score + 4th_qtr_score PERSISTED DECIMAL(5,2));INSERT INTO player_scores VALUES('Steve', '119','22.50','72.00', '63.00', '45.00'),('Jack', '432', '90.10', '78.00','88.20', '92.20'),('Jim', '779','75.00', '68.90','55.70', '72.00'),('Eve', '189','91.50', '88.10', '95.00', '94.50'),('Shelia','338', '75.60', '72.00', '81.10', '78.40'),('June', '521', '81.00', '87.30','76.80','82.20'),('Martin', '674','98.80', '95.10', '88.00', '96.40')
SELECT player_name, 1st_qtr_score, 2nd_qtr_score,bit_xor(3rd_qtr_score) OVER (ORDER BY 2nd_qtr_score)FROM player_scoresORDER BY player_name;
+-------------+---------------+---------------+-----------------------------+
| player_name | 1st_qtr_score | 2nd_qtr_score | bit_xor(3rd_qtr_score) OVER |
| | | |(ORDER BY 2nd_qtr_score) |
+-------------+---------------+---------------+-----------------------------+
| Eve | 91.50 | 88.10 | 28 |
| Jack | 90.10 | 78.00 | 14 |
| Jim | 75.00 | 68.90 | 56 |
| June | 81.00 | 87.30 | 67 |
| Martin | 98.80 | 95.10 | 68 |
| Shelia | 75.60 | 72.00 | 86 |
| Steve | 22.50 | 72.00 | 86 |
+-------------+---------------+---------------+-----------------------------+Last modified: February 23, 2023