Bitwise XOR operator

SQL Server Query

            
-- Simplified example to demonstrate XOR  

WITH CustomerPreferences AS (  

    SELECT 1 AS CustomerID, 1 AS Preferences UNION ALL -- Only Email  

    SELECT 2, 2 UNION ALL -- Only Phone  

    SELECT 3, 3 UNION ALL -- Both  

    SELECT 4, 0       -- Neither  

)  

SELECT  

    CustomerID,  

    Preferences,  

    CASE  

        WHEN (Preferences & 1) = 1 THEN 'Prefers Email'  

        ELSE 'Does not prefer Email'  

    END AS PrefersEmail,  

    CASE  

        WHEN (Preferences & 2) = 2 THEN 'Prefers Phone'  

        ELSE 'Does not prefer Phone'  

    END AS PrefersPhone,  

    CASE  

        WHEN (Preferences ^ 3) = 0 THEN 'Prefers Both'  

        WHEN (Preferences ^ 1) = 0 THEN 'Prefers Only Email'  

        WHEN (Preferences ^ 2) = 0 THEN 'Prefers Only Phone'  

        ELSE 'Prefers Neither or Mixed'  

    END AS PreferenceCategory  

FROM CustomerPreferences;  

            
        

Create SQL query with SqlQueryBuilder

            
var (sql, parameters) = new SqlQueryBuilder()  

.WithCTETable(new Table("CustomerPreferences"), new SqlQueryBuilder()  

    .Select()  

    .Column(1, "CustomerID").Column(1, "Preferences")  

    .UnionAll(new SqlQueryBuilder()  

        .Select()  

        .Column(2, "CustomerID").Column(2, "Preferences")  

        .UnionAll(new SqlQueryBuilder()  

            .Select()  

            .Column(3, "CustomerID").Column(3, "Preferences")  

            .UnionAll(new SqlQueryBuilder()  

                .Select()  

                .Column(4, "CustomerID").Column(0, "Preferences")  

            )  

        )  

    )  

)  

.Select()  

.Columns("CustomerID","Preferences")  

.Column(new CASE().When(new ColumnArithmatic("Preferences").BitwiseAND(1).Equale(1))  

    .Then("Prefers Email").Else("Does not prefer Email"), "PrefersEmail")  

.Column(new CASE().When(new ColumnArithmatic("Preferences").BitwiseAND(2).Equale(2))  

    .Then("Prefers Phone").Else("Does not prefer Phone"), "PrefersPhone")  

.Column(new CASE()  

    .When(new ColumnArithmatic("Preferences").BitwiseXOR(3).Equale(0)).Then("Prefers Both")  

    .When(new ColumnArithmatic("Preferences").BitwiseXOR(1).Equale(0)).Then("Prefers Only Email")  

    .When(new ColumnArithmatic("Preferences").BitwiseXOR(2).Equale(0)).Then("Prefers Only Phone")  

    .Else("Prefers Neither or Mixed")  

, "PreferenceCategory")  

.From("CustomerPreferences")  

.Build(); 

            
        

Query build by SqlQueryBuilder

            
WITH CustomerPreferences
AS (SELECT @pMAIN_2507192004517564470 AS CustomerID,
           @pMAIN_2507192004517564471 AS Preferences
    UNION ALL
    SELECT @pMAIN_2507192004517564472 AS CustomerID,
           @pMAIN_2507192004517564473 AS Preferences
    UNION ALL
    SELECT @pMAIN_2507192004517564474 AS CustomerID,
           @pMAIN_2507192004517564475 AS Preferences
    UNION ALL
    SELECT @pMAIN_2507192004517564476 AS CustomerID,
           @pMAIN_2507192004517564477 AS Preferences)
SELECT CustomerID,
       Preferences,
       CASE WHEN Preferences & @pMAIN_2507192004517564478 = @pMAIN_2507192004517564479 THEN @pMAIN_250719200451756447_10 ELSE @pMAIN_250719200451756447_11 END AS PrefersEmail,
       CASE WHEN Preferences & @pMAIN_250719200451756447_12 = @pMAIN_250719200451756447_13 THEN @pMAIN_250719200451756447_14 ELSE @pMAIN_250719200451756447_15 END AS PrefersPhone,
       CASE WHEN Preferences ^ @pMAIN_250719200451756447_16 = @pMAIN_250719200451756447_17 THEN @pMAIN_250719200451756447_18 WHEN Preferences ^ @pMAIN_250719200451756447_19 = @pMAIN_250719200451756447_20 THEN @pMAIN_250719200451756447_21 WHEN Preferences ^ @pMAIN_250719200451756447_22 = @pMAIN_250719200451756447_23 THEN @pMAIN_250719200451756447_24 ELSE @pMAIN_250719200451756447_25 END AS PreferenceCategory
FROM CustomerPreferences;


            
        

Parameters (If used)

Query Results: