SQL Bitwise XOR Operator

SQL Bitwise XOR Operator

The SQL Bitwise XOR ( ^ ) operator performs a bitwise exclusive OR operation between two integer values. It compares the binary representation of both numbers and returns 1 for each bit position where the bits are different, and 0 where they are the same.

In this tutorial, we will explore the SQL Bitwise XOR operator, its syntax, and practical examples.

---

Syntax of SQL Bitwise XOR Operator

The ^ operator is used to perform a bitwise XOR operation in SQL.

SELECT number1 ^ number2 AS result;

Explanation:

• The ^ operator compares the binary values of number1 and number2.
• If the bits in a given position are different, the result will have 1 at that position.
• If the bits are the same, the result will have 0 at that position.

---

Step-by-Step Examples Using SQL Bitwise XOR Operator

1 Simple Bitwise XOR Operation

Let’s perform a bitwise XOR operation between 5 and 3:

SELECT 5 ^ 3 AS result;

Explanation:

First, we convert the numbers to binary:

Decimal	Binary
5	101
3	011

Performing the XOR operation bit by bit:

Bit Position	5 (101)	3 (011)	Result
1	1	1	0
2	0	1	1
3	1	0	1

The binary result is 110, which is 6 in decimal. So the query returns:

---

2 Using Bitwise XOR in a Table Query

Now, let’s create a users table to demonstrate a practical use case of XOR:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    access_code INT
);

Insert some sample data:

INSERT INTO users (name, access_code)
VALUES 
('Arjun', 7),
('Ram', 10),
('John', 12),
('Priya', 15);

Now, let’s assume that we want to encrypt the access_code using a secret key (5) with the XOR operation:

SELECT name, access_code, (access_code ^ 5) AS encrypted_code
FROM users;

Explanation:

• The XOR operation is performed between the access_code and 5 to generate an encrypted value.
• Since XOR is reversible, applying the same operation again will retrieve the original access_code.

---

Conclusion

The SQL Bitwise XOR (^) operator is useful for performing bitwise computations and encrypting values. In this tutorial, we covered:

1. How the Bitwise XOR operator works.
2. A simple bitwise XOR operation.
3. Using XOR to encrypt and decrypt values in a database table.