It has been a long time since we have puzzle on this blog so let us have a quick puzzle this Friday. It is based on Bit Wise Operator and very simple if you know those operators.
We will guess the output of the SQL script.
For example, here is the select statement:
SELECT 10 &Â 10
Upon executing it will return us answer as a 10.
However, when I execute following two queries, both of the queries give an answer as Zero.
SELECT 10 &Â 0;
SELECT 1 &Â 100;
Here is the question for you – why is it giving answers as zero? Please leave your answer in a comment below.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
20 Comments. Leave new
1010 & 0000 = 0
1100100 & 0000001 = 0
Because bit wise & operation is zero.
e.g. 1
1: 1010
100: 1010
—————
& : 1010 (i.e. 10)
e.g. 2
10: 1010
0: 0000
————–
& : 0000 (i.e. 0)
e.g. 3
1: 0000001
100: 1100100
—————
& : 0000000 (i.e. 0)
It’s interesting.
Binary number operation
SELECT 10 & 0
=> 1010 & 0000 in binary => 0000
SELECT 1 & 100
=> 0000001 & 1100100 in binary => 0000000
Another example is “SELECT 11 & 14; ”
=> 1011 & 1110 in binary => 1010 in binary => 10
It returns zero because of the logical & operation. The first and second bit in each position is not 1. For example: SELECT 111 & 111 equals 111 because each bit in each position is 1, also SELECT 10 &100 equals 0 because the first and second bit in each position is not 1.
bin Dec
00000000 = 0
00000001 = 1
00001010 = 10
01100100 = 100
“The bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1; otherwise, the bit in the result is set to 0.”
So, we also can try “10 & 100” and result well be 0.
Its bitwise and operator..
100
001
Anding from left to right as we do while adding when we were kid.. Gives..
000
And operator works as
0 & 0 is 0
0& 1 is 0
1 & 0 is 0
1 & 1 is 1
Nice trick Sir.. I was wondering what would be a real world scenario of using it..
sometime you have enum define and you need to display data in report which is not in .net.
For example
SQL Server always stores Numbers in “Right” precedence.
Ex: 1 & 10 is actually takes the values into buffers as 00000001 and 00000010 . so, if we apply a bit AND the result is 00000000
10 & 00 = 00
001 & 100 = 000
Applying ‘&’ operator on each bit, you get zeroes.
100 = 01100100
1 = 00000001
1100100 & 1 = zero in common
its a bit wisae operation and its tration like and operator
10 & 10 both condition are true it will return 1(10)
and for rest it will false ie (0)
In TSQL bitwise AND (&) operator takes integer values and Convert it into Binary Values by self
And then does bitwise comparison on these 2 Expressions.
AS in example given by you
select 10&10
will be translated as
Select 1010&1010
And the result will be
1010 and then this result is again translated to integer values which is 10
Whether in case of
Select 1&100
this will be translated as
Select 1&1100100 will result in 0 and this will be translated to Integer as 0
10 in binary format equals by 1010
When you perform the bitwise with 0 you get 0
Same with 100 which it’s right most number in binary format is 0 so the result of the logical and with any other number would be 0
Guys, you know it not!
In the first example we need to multiply: 10 x 0 = 0
In the second example we just remove heading “1” from both parts: 100=>00, 1 => 0; now multiply 00 x 0 = 0
Much better explanation than all that bitwisemagic :D
select 10 & 10
here we are comparing the same value it means simple
if(T&T)//it will return true value
{
select T;
}
else
{
select F;
}
select 0 & 1
here we are comparing the same value it means simple
if(F&T)//it will return false value
{
select T;
}
else
{
select F;
}
select 100 & 0
here we are comparing the same value it means simple
if(F&T) //it will return false value
{
select T;
}
else
{
select F;
}
The binary representation of 1 is 0000 0000 0000 0001. The binary representation of 10 is 0000 0000 0000 1010. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 0000, which is decimal 11.
(A & B)
0000 0000 0000 0001
0000 0000 0000 1010
——————-
0000 0000 0000 0000 = 0
The binary representation of 1 is 0000 0000 0000 0001. The binary representation of 10 is 0000 0000 0110 0100 . Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 0000, which is decimal 11.
(A & B)
0000 0000 0000 0001
0000 0000 0110 0100
——————-
0000 0000 0000 0000 = 0
The & operator does a bitwise AND on both the integer values by converting them to their binary form.
For eg SELECT 1 & 100;
00000001 —- binary value of 1
01100100 —- binary value of 100
—————-
00000000 — (After applying the AND operator) decimal value of 0
For select 10 & 10
00001010 — binary value of 10
00001010 — binary value of 10
————–
00001010 — (After applying the AND operator) decimal value of 10
Small correction – I meant the output is binary value of 0 & 10 respectively
binary value for 1 and 0 is always 0000 which makes the result 0 (with AND operation)
Please let me know whether this is right?
select 10 & 0
select 10 & 100
10= 00001010
0= 00000000
——————
00000000
Sum of this =0
10= 00001010
100=01100100
——————
00000000
Sum of this =0