Answered by:
Logical XOR operator in TSQL (SQL Server 2000)

Hi all....
I was wondering why SQL Server 2000 lacks a logical XOR operator. Do you have a ready to use function that implements it?
How can I program one by myself? I know that A XOR B is equivalent to NOT A * B + A * NOT B. So I tried to create this function :
CREATE FUNCTION XOR
(
 Add the parameters for the function here
@A BIT,
@B BIT
)
RETURNS BIT
AS
BEGIN
RETURN NOT @A AND @B OR @A + NOT @B
END
But it didn't compile.
How can I pass boolean values as parameters? any other hint?
Thanks
Jaime
Question
Answers

There is no boolean data type in TSQL and there are only few system builtins like CONTAINS that can be used directly in a WHERE clause. So you need to use the XOR operator and test for the return value of expression like:WHERE (case A when 10 then 1 else 0 end)^(case B when 5 then 1 else 0 end) = 1
All replies


As you said, that is a bitwise operator, not a logical one.
For example, using the ^ I couldn't do something like :
SELECT *
FROM TABLE
WHERE (A = 10) ^ (B = 5)
which should return records when either A = 10 or B = 5, but if conditions are both satisfied or both not satisfied, I don't want records to be returned.
Another suggestion to accomplish that? Of course this is a simplified scenario. In case of 2 conditions I can do perfectly :
WHERE (A <> 10 AND B = 5) OR (A = 10 AND B <> 5)
The only solution I see is to implement a XOR function myself and to nest them, but I couldn't (or don't know how) pass a boolean value as a function parameter. If you know how, please tell me.
Thanks
Jaime

There is no boolean data type in TSQL and there are only few system builtins like CONTAINS that can be used directly in a WHERE clause. So you need to use the XOR operator and test for the return value of expression like:WHERE (case A when 10 then 1 else 0 end)^(case B when 5 then 1 else 0 end) = 1

The reason your UDF does not work is that BIT is not the same as BOOLEAN. There is no Boolean type in SQL Server, so you can't do:SELECT *FROM TableWHERE ColumnInstead, (assuming that Column is typed as BIT) you have to do:SELECT *FROM TableWHERE Column = 1Anyway, it's quite easy to do XOR inline:SELECT *FROM TableWHERE(Column1 = 1 AND Column2 <> 1)OR (Column1 <> 1 AND Column2 = 1)

Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
<Jaime Stuardo@discussions.microsoft.com> wrote in message news:b264989113054b27a0745b0a896807e8@discussions.microsoft.com...Hi all....
I was wondering why SQL Server 2000 lacks a logical XOR operator. Do you have a ready to use function that implements it?
How can I program one by myself? I know that A XOR B is equivalent to NOT A * B + A * NOT B. So I tried to create this function :
CREATE FUNCTION XOR
(
 Add the parameters for the function here
@A BIT,
@B BIT
)
RETURNS BIT
AS
BEGIN
RETURN NOT @A AND @B OR @A + NOT @B
END
But it didn't compile.
How can I pass boolean values as parameters? any other hint?
Thanks
Jaime

Just try this Code:
it works fine in SQL server 2005 & 2008 , I haven't SQL 2000 But I'm sure it works fine.
create FUNCTION XOR (@A BIT, @B BIT ) RETURNS BIT
AS
BEGIN
if ( ( @A = 0 ) and (@B = 1) ) OR ( (@A = 1) and (@B = 0) )
begin
return 1
end
return 0
END
Farzad Jalali

I have these two views , one of them hard coded as NULL as it always needs to be NULL. I wanted to union them
Now the problem is I have to replace those NULLS if I have a value from other VIEW or else NULL ( no change)
I am getting duplicates of rows with values and NULLS , whereas I needed only records with values
I need records with values and records without value as NULL . please let me know how to eliminate dups with NULLS
21
ARGH
Bus
NULL
NULL
NULL
NULL
21
ARGH
Bus
66
781
HEAVY
00:00.0
21
F1WS
Ship
NULL
NULL
NULL
NULL
21
HGDD
car
NULL
NULL
NULL
NULL
21
HGDT
car
NULL
NULL
NULL
NULL
Below is the code :
 view having values
SELECT *
FROM Table1 AS S INNER JOIN
Table2 AS P ON S.columnA = P.columnB
WHERE EXISTS
(SELECT 1 AS A
FROM Table2 AS R
WHERE (S.columnA = columnB))
GROUP BY ALL
UNION
 views harded coded as NULLS
SELECT *, NULL AS APR , NULL AS MAY , NULL AS JUN , NULL AS JUL , NULL AS AUG
FROM Table1 AS S INNER JOIN
Table2 AS P ON S.columnA <> P.columnB
WHERE (NOT EXISTS
(SELECT 1 AS A
FROM Table2 AS R
WHERE (S.columnA = columnB) )) OR
EXISTS
(SELECT 1 AS A
FROM Table2 AS R
WHERE (S.columnA = columnB) )
GROUP BY ALL



Hi Nicorac,
SqlServer 2000 already has the bitwise XOR operator. So your function could simply do: RETURN (@A ^ @B)
Unfortunatelly SqlServer lacks the ability to pass a boolean expression as parameter (well, at least a reasonable way of doing it). There is no boolean data type and boolean expressions are not directly convertible to integer type. A UDF function is useless in this context.
Carlos.

why not just
CREATE FUNCTION XOR ( @A BIT ,@B BIT ) RETURNS BIT AS BEGIN DECLARE @Return BIT SET @Return = CASE WHEN @A<>@B THEN 1 ELSE 0 END RETURN @Return END
or
CREATE FUNCTION [dbo].[XOR] ( @A BIT ,@B BIT ) RETURNS BIT AS BEGIN DECLARE @Return BIT SELECT @Return = 1 WHERE @A<>@B RETURN ISNULL(@Return,0) END GO
 Proposed as answer by Daniel Chafen Thursday, August 25, 2011 10:26 AM