SQL Puzzle – A Quick Fun with Bit Wise Operator – Part 2

This is a follow up, puzzle of SQL Puzzle – A Quick Fun with BitWise Operator. Lots of people really like the earlier puzzle where I have used Bit Wise Operator AND and build a simple puzzle. Today we are going to use another bit wise operator – OR.

First execute following query:

SELECT 10 | 10

It will return us result as a 10.

SQL Puzzle - A Quick Fun with Bit Wise Operator - Part 2 bitwiseor1

Now execute following query:

SELECT 10 | 0

The above query will return us result as a 10 as well.

SQL Puzzle - A Quick Fun with Bit Wise Operator - Part 2 bitwiseor2

Now let us execute following query:

SELECT 1 | 100

It will return us result as a 101.

SQL Puzzle - A Quick Fun with Bit Wise Operator - Part 2 bitwiseor3

Here is the question back to you – Why does the last query return us result as a 101 when we have used Bit Wise Operator OR?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL Puzzle – A Quick Fun with Bit Wise Operator
Next Post
Developers – A Fun List of Things Related to Developer

Related Posts

7 Comments. Leave new

  • Nithin Gangadharan
    February 8, 2014 9:27 am

    ‘|’ does ORing on binary representation of a given no.
    E.g. 1. 10|10
    1010
    | 1010 = 1010 which is 10
    2. 10|0
    1010
    | 0000 = 1010 which is again 10

    Similarly 1|100
    0000001
    | 1100100 = 1100101 which is 101
    Hence the answer.

    Reply
  • Ajay Gopal Shrestha
    February 8, 2014 12:19 pm

    Or table
    0 | 0 = 0
    0 | 1 = 1
    1 | 0 = 1
    1 | 1 = 1

    100
    001

    Result: 101

    Reply
  • Just Like your yesterday’s puzzle.
    This bitwise OR (|) operator also takes integer values as Parameters and convert them to Binary by self, and do bit by bit comparison.

    like:

    SELECT 1|100
    will be translated as
    select
    0000001
    1100100
    and returns
    1100101 (101 in decimal)
    and this will be translated as integer before displaying

    Reply
  • First of all, the result for last SELECT is wrong here, it is 101, not 10 as you show :)
    Second, in the bitwise OR the rule is 1 | x = 1 for x either 0 or 1 and only 0 | 0 = 0.
    100 has the last binary digit 0 (it divides by 2) and when we do 1 | 100 we get 101, because of the OR rule (1 | 0 = 1, 0 being the last binary digit of 100)

    You can write this operation in binary mode:

    1 | 1100100 = 1100101 = 101

    Reply
  • Pinal, your “Results” image for “SELECT 1 | 100” is incorrect — it should show a result of “101” rather than a result of “10”. The answer is 101 because the bitwise-OR returns a 1 for each bit that is one in either of the expressions — it should be obvious that 0 | 100 would result in 100, and the first expression, 1, adds a bit to the ones column, hence a result of 101.

    Reply
  • Binary for 100 – 1100100

    So 100 | 1 => 1100100 | 0000001 => 1100101 => 101

    Reply
  • Rakesh Lal Dewangan
    February 11, 2014 1:34 am

    The logical OR operator is similar to AND as it works with two values. The difference is that where either of the bits at a specific location is set, the resultant bit will also be set. If both operands contain a zero at a position, the resultant bit will be clear. For example:
    00000001 = 1
    01100100 = 100
    OR
    01100101 = 101
    So Select 1|100 would be 101.

    Reply

Leave a Reply

Menu