Logical XOR operator in T-SQL (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 :

CREATEFUNCTION XOR

(

-- Add the parameters for the function here

@.A BIT,

@.B BIT

)

RETURNS BIT

AS

BEGIN

RETURNNOT @.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

There is an XOR operator -- it is the caret (^). In the Books On-line look up operators(symbols) bitwise.|||

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 built-ins 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 Table WHERE Column Instead, (assuming that Column is typed as BIT) you have to do: SELECT * FROM Table WHERE Column = 1 Anyway, it's quite easy to do XOR inline: SELECT * FROM Table WHERE (Column1 = 1 AND Column2 <> 1) OR (Column1 <> 1 AND Column2 = 1) -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jaime Stuardo@.discussions.microsoft.com> wrote in message news:b2649891-1305-4b27-a074-5b0a896807e8@.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

Logical XOR

How do I write this in T-SQL?

Topic Is Null XOR TopicKey Is Null

Currently I write this,

(Topic Is Null OR TopicKey Is Null) AND (Topic Is NOT Null OR TopicKey Is NOT Null)

but that starts to get really long when I have three or four values.

Jonathan Allen

Of dubious value…

CREATE TABLE dbo.XOR

(

id int

,this varchar(10)

,that varchar(10)

,what varchar(10)

,how varchar(10)

)

GO

INSERT INTO dbo.XOR (id,this,that,what,how) VALUES(1,'a', 'b','c','d')

INSERT INTO dbo.XOR (id,this,that,what,how) VALUES(2,'a', 'b','c',NULL)

INSERT INTO dbo.XOR (id,this,that,what,how) VALUES(3,'a', 'b',NULL,'d')

INSERT INTO dbo.XOR (id,this,that,what,how) VALUES(4,'a', 'b','c',NULL)

INSERT INTO dbo.XOR (id,this,that,what,how) VALUES(5,NULL, 'b','c','d')

INSERT INTO dbo.XOR (id,this,that,what,how) VALUES(6,'a', NULL,'c','d')

INSERT INTO dbo.XOR (id,this,that,what,how) VALUES(7,'a', NULL,'c',NULL)

SELECT *

FROM dbo.XOR

WHERE LEN(this) ^ LEN(that) IS NULL

OR LEN(what) ^ LEN(how) IS NULL

idthisthatwhathow

2abcNULL

3abNULLd

4abcNULL

5NULLbcd

6aNULLcd

7aNULLcNULL

See SQL Server 2005 Books Onlinetopic:

^ (Bitwise Exclusive OR) (Transact-SQL)

|||

You can do below:

case when Topic is null then 1 else 0 end ^ case when TopicKey is null then 1 else 0 end = 1

Note however this rewrite will not be able to use any index on the Topic/TopicKey columns efficiently - no seeks. So this may or may not work depending on your schema.