none
Logical XOR operator in T-SQL (SQL Server 2000)

    Question

  • 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

    Tuesday, September 05, 2006 5:29 PM

Answers

  • 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
    Tuesday, September 05, 2006 7:25 PM

All replies

  • There is an XOR operator -- it is the caret (^). In the Books On-line look up operators(symbols) bitwise.
    Tuesday, September 05, 2006 6:41 PM
  • 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

    Tuesday, September 05, 2006 7:21 PM
  • 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
    Tuesday, September 05, 2006 7:25 PM
  • 
    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 Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    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

    Tuesday, September 05, 2006 7:27 PM
  •  

    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

    Friday, June 20, 2008 2:36 PM
  • 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

    Tuesday, June 24, 2008 7:53 PM
  • This is the SqlServer 2000 version of XOR function:

    CREATE FUNCTION XOR 
    (
        @A BIT,
        @B BIT
    )
    RETURNS BIT
    AS
    BEGIN
        RETURN ((~@A & @B) | (@A & ~@B))
    END
    GO
    
    Tuesday, January 20, 2009 8:30 AM
  • SELECT *
    FROM Table
    WHERE cast(Column1 as int) + cast(Column2 as int) = 1
    • Edited by Caracrist Sunday, May 24, 2009 9:50 AM font
    Sunday, May 24, 2009 9:48 AM
  • 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.

    Tuesday, April 19, 2011 1:55 PM
  • 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
    Thursday, August 25, 2011 10:20 AM