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.
