[sql-server] T-SQL XOR Operator

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

The xor operator is ^

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

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

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

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)

How about this?

(A=1 OR B=1 OR C=1) 
AND NOT (A=1 AND B=1 AND C=1)

And if A, B and C can have null values you would need the following:

(A=1 OR B=1 OR C=1) 
AND NOT ( (A=1 AND A is not null) AND (B=1 AND B is not null) AND (C=1 AND C is not null) )

This is scalable to larger number of fields and hence more applicable.

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

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.

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

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