T-SQL XOR Operator

Question

Is there an XOR operator or equivalent function in SQL Server (T-SQL)?

I guess i was trying to use on nullable varchar columns. Example: WHERE (Note is null) ^ (ID is null). I am getting "Incorrect syntax near '^'" — ses011, Mar 23 '11 at 21:35
http://stackoverflow.com/questions/6007052/is-there-an-exclusive-or-operator-in-t-sql — Mr. TA, Jun 24 '15 at 18:27
Note that Microsoft SQL distinguishes between **(1)** bitwise operators (such and |, &, ^ [link](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-operators-transact-sql)) used on bits (i.e. 1s and 0s) that make up integer values, and **(2)** logical boolean (i.e. true and false) operators used within a WHERE clause or CASE statement etc. (This is surprising, since in computers (where MS claims to specialise), boolean and logical values are the same, i.e. 1= true, 0 = false). You seem to be asking about the logical operators, since your comment mentions "WHERE". — Reversed Engineer, May 12 '17 at 08:47
As far as I can tell, even SQL 2017 does not have logical XOR operator, so you can simulate it with (A OR B) AND NOT (A AND B), or alternatively (A AND NOT B) OR (B AND NOT A). Ok, now read that all slowly again ha ha :) It's all NOT FALSE — Reversed Engineer, May 12 '17 at 08:48
If SQL Server had booleans as a first class type, you could do exclusive or on them by comparing with `=`, but alas, it doesn't. — jpmc26, Sep 09 '17 at 04:44

score 61 · Accepted Answer · answered Mar 23 '11 at 21:18

61

There is a bitwise XOR operator - the caret (^), i.e. for:

SELECT 170 ^ 75

The result is 225.

For logical XOR, use the ANY keyword and NOT ALL, i.e.

WHERE 5 > ANY (SELECT foo) AND NOT (5 > ALL (SELECT foo))

answered Mar 23 '11 at 21:18

Nathan Rivera

766
5
4

Can you explain in more detail what is happening in your second example? Where 5 is greater than anything in the foo column but not greater than everything in the foo column. Is that how it is read? – ses011 Mar 23 '11 at 21:34
3

Yes, you are reading it correctly. XOR is true if one or more of the conditions are met, but is false if zero or all of the conditions are met. We can use ANY to see if any one or more of the values in the set meet the condition. The NOT ... ALL part of it ensures that the expression returns false if all of the conditions are met. – Nathan Rivera Mar 23 '11 at 22:26
1

Huh, very interesting. A part of T-SQL I haven't really dealt with. – ses011 Mar 24 '11 at 01:45
9

I don't really see how this answers the question as clarified in the example in his comment on the question. – djs May 24 '13 at 22:14
1

Please note, the link from HarveyFrench is specific to SQL 2014 and later. Also, IT IS RELEVANT because it applies to bitwise operations which the OP seems to be asking (the OP used "^" which is a bitwise operator.) However, in an attempt to help others, I would like to point out that non-bitwise XOR won't be available until SQL 2016. – Andrew Steitz Jan 06 '16 at 17:46
Word of caution: this `ANY` logic only works when there are two conditions/inputs because XOR is NOT equivalent to the claimed 'XOR is true if one or more of the conditions are met'. When there are only 2 'conditions' (inputs), then yes, this is true and it works. But when there are more than 2 'conditions' (inputs), then this is not equivalent. – Shawn Kovac Jun 06 '17 at 17:49
true XOR true XOR false = false. but i believe the `ANY` logic wud yield a 'true' result for this given example. – Shawn Kovac Jun 06 '17 at 17:50

score 43 · Answer 2 · answered Feb 06 '13 at 14:34

43

Using boolean algebra, it is easy to show that:

A xor B = (not A and B) or (A and not B)


A B | f = notA and B | g = A and notB | f or g | A xor B    
----+----------------+----------------+--------+--------    
0 0 | 0              | 0              | 0      | 0    
0 1 | 1              | 0              | 1      | 1    
1 0 | 0              | 1              | 1      | 1    
1 1 | 0              | 0              | 0      | 0

answered Feb 06 '13 at 14:34

Alberto De Caro

5,147
9
47
73

This just saved my day, thanks! (TinyTDS on rails, what a headache) – JeanLescure Sep 01 '14 at 17:12
12

This is unsuitable if your conditions are long. – jpmc26 Sep 09 '17 at 04:45
if you have 5 fields to XOR, good luck in forming the combinations!! – Raheel Hasan May 08 '20 at 06:50

Shawn Kovac · Answer 3 · 2016-01-22T16:22:37.610

As clarified in your comment, Spacemoses, you stated an example: WHERE (Note is null) ^ (ID is null). I do not see why you chose to accept any answer given here as answering that. If i needed an xor for that, i think i'd have to use the AND/OR equivalent logic:

WHERE (Note is null and ID is not null) OR (Note is not null and ID is null)

That is equivalent to:

WHERE (Note is null) XOR (ID is null)

when 'XOR' is not available.

score 18 · Answer 4 · edited Nov 16 '16 at 13:56

18

MS SQL only short form (since SQL Server 2012):

1=iif( a=b ,1,0)^iif( c=d ,1,0)

edited Nov 16 '16 at 13:56

VV5198722

374
5
19

answered Jan 19 '14 at 14:06

was

333
3
7

1

That's cute. I'd comment it unless it was a common short cut in my shop. I curious about how the query optimizer treat that vs the more explicit answer from @shawn-kovac. But not curious enough to stop and test it. – Karl Kieninger May 12 '15 at 15:06
1

This should be the best answer because a=b and c=d are invoked only once. This is important in cases where the expressions are expensive. The question asks about XOR acting on logical expressions. – xxyzzy Jan 26 '16 at 12:50
1

Be aware this has a different behavior with null values. The result may be different from "(a = b and c <> d) or (a <> b and c = d)" if one of the values is null. – Ricardo Rocha Feb 05 '20 at 11:18

Sebi · Answer 5 · 2012-07-18T20:06:54.723

5

The xor operator is ^

For example: SELECT A ^ B where A and B are integer category data types.

edited Jul 18 '12 at 20:06

answered Mar 23 '11 at 21:09

Sebi

1,390
1
13
22

That doesn't pass syntax validation: – simon Feb 01 '12 at 16:31
this does not answer the original problem. yes it does answer the question how it was asked, but if you read the comments, the first comment asks 'why?' because there are 2 kinds of very different XOR's. one is bitwise XOR. the other is logical XOR. the question does not make it clear which one he's asking. but the comment asks which he needs and his true need was NOT the bitwise XOR, which is '^'. Thus this answer does not solve the requested problem. – Shawn Kovac Jun 06 '17 at 17:36
the comments to the question clarify that he's not needing XOR for integers, which is 'bit-wise' XOR. the real question is for a logical xor, not bit-wise XOR, that is used like `WHERE (Note is null) ^ (ID is null)`. Note these are not integer XOR operations. – Shawn Kovac Jun 06 '17 at 17:39
1

@ShawnKovac Thanks for the clarification, but you should notice that I posted this answer 6 years ago, several minutes before the OP has cleared that up, and when there wasn't yet a better answer - and also I don't think it is wrong to answer the question with an answer other than the one that OP is looking for, as long as it can be useful to people who search for this question, as they may be looking for this solution. – Sebi Jun 09 '17 at 09:52
@Sebi, ah, i hadn't noticed that you answered this before the mentioned clarification. thanks for enlightening me to this. my bad. i'll pay more attention to such in the future, and thanks for sharing so i can be more attuned to such in the future, Sebi. and yes, i agree that it's okay to answer a similar question for others who may find the answer useful. i apologize that i didn't even thank you for your answer. but i do that now: Thanks for your answer, Sebi, and thanks especially for teaching me what i learned from your last comment! i give you a +1 for your wisdom in both aspects. – Shawn Kovac Jun 14 '17 at 19:45
1

@ShawnKovac might be worth editing the question rather than commenting on this answer – user1007074 Nov 07 '18 at 17:44

SQLMenace · Answer 6 · 2011-03-23T21:14:07.980

2

It is ^ http://msdn.microsoft.com/en-us/library/ms190277.aspx

See also some code here in the middle of the page How to flip a bit in SQL Server by using the Bitwise NOT operator

edited Mar 23 '11 at 21:14

answered Mar 23 '11 at 21:08

SQLMenace

132,095
25
206
225

Beat me to it. Note: Available in SQL Server 2005+. Also note: This is a bitwise and not a logical operator. – Dan J Mar 23 '11 at 21:09
Is available in 2000 http://msdn.microsoft.com/en-us/library/aa276869(SQL.80).aspx – SQLMenace Mar 23 '11 at 21:13
this does not answer the original problem. yes it does answer the question how it was asked, but if you read the comments, the first comment asks 'why?' because there are 2 kinds of very different XOR's. one is bitwise XOR. the other is logical XOR. the question does not make it clear which one he's asking. but the comment asks which he needs and his true need was NOT the bitwise XOR, which is '^'. Thus this answer does not solve the requested problem. – Shawn Kovac Jun 06 '17 at 17:35

score 2 · Answer 7 · answered Feb 27 '19 at 12:31

2

<> is generally a good replacement for XOR wherever it can apply to booleans.

answered Feb 27 '19 at 12:31

David Larochette

1,200
10
18

That would be true only if you use a CASE expression. Example: CASE WHEN A <> B THEN 1 ELSE 0 END – PollusB Jan 09 '20 at 14:52

score 1 · Answer 8 · answered Oct 06 '18 at 06:01

1

From your comment:

Example: WHERE (Note is null) ^ (ID is null)

you could probably try:

where
   (case when Note is null then 1 else 0 end)
 <>(case when ID is null then 1 else 0 end)

answered Oct 06 '18 at 06:01

Gregor y

1,762
15
22

T-SQL XOR Operator

8 Answers8

Linked