Why does SELECT 2^3 return 1 in SQL Server? [duplicate]

This question already has answers here: Closed 10 years ago. 开发者_如何学Go

Possible Duplicate:

What does a caret (^) do in a SQL query?

Why does SELECT 2^3 return 1 in SQL Server ?

The above was an interview question I came across and could not get why it returns 1.

After googling a bit, I found out that it is a bitwise operator. But I still couldn't understand why 1 is an output.

I have basic knowledge of queries, stored procedure and T-SQL. Can anybody please explain to me:

1. How do I get 1 in SELECT 2^3 ?
2. What is the practical use of such operators ?

And if there is a practical use, then what are the best practices while using such operators

---

Because ^ is XOR operator.

Truth table for XOR

-------
|^|1|0|
-------
|1|0|1|
-------
|0|1|0|
-------

In another words in result we have have one only when two bits are different.

1 ^ 1 = 0
1 ^ 0 = 1
0 ^ 1 = 1
0 ^ 0 = 0

For You case it its

binary - decimal
00010  - 2
00011  - 3
---------- ^
00001  - 1

More about XOR gate

Usage

For example for mask (bitwise operations) handling or cryptography.

---

a b a^b
-------
0 0  0
0 1  1
1 0  1
1 1  0

2   0b10
3   0b11
--------
2^3 0b01 = 1

The real practical use is to toggle bits when used as flags.

---

it's the Bitwise Exclusive OR.

The truth tables for OR and XOR (exclusive OR) are different. OR means 'If either of these inputs are true, the output is true'. XOR means 'If one or the other, but not both of the inputs are true, the output is true'

OR truth table: false OR false = false true OR false = true false OR true = true true OR true = true

XOR truth table: false XOR false = false true XOR false = true false XOR true = true true XOR true = false

So what the query is doing is converting each input into binary, then doing an XOR on each bit. 2 ^ 3:

    00000010 (2) 
XOR
    00000011 (3)
= 
    00000001

---

2 in binary = 10
3 in binary = 11
^ bitwise (XOR)

10 XOR 11 = 01
01 binary = 1 in decimal

---

It seems you need to use "power" function? :)

POWER Returns the value of the given expression to the specified power.

Syntax POWER ( numeric_expression , y )

select power(2, 3)

returns 8...

PS: Using ^ operator (and other bitwize operators) is need when you want to interpret some numeric fields as "mask" data and perform operations using bits from numbers.