T-SQL XOR Operator

Question

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

User · Answer

The xor operator is    For example  SELECT A   B where A and B are integer category data types

User · Answer

MS SQL only short form  since SQL Server 2012    1 iif  a b  1 0  iif  c d  1 0

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  ANY  SELECT foo  AND NOT  5  gt  ALL  SELECT foo

User · Answer

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    lt  gt  case when ID is null then 1 else 0 end

User · Answer

lt  gt  is generally a good replacement for XOR wherever it can apply to booleans

User · Answer

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

[sql-server] T-SQL XOR Operator

Examples related to sql-server

Examples related to tsql

Examples related to xor