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_2602032108437581180 AS CustomerID,
           @pMAIN_2602032108437581181 AS Preferences
    UNION ALL
    SELECT @pMAIN_2602032108437581182 AS CustomerID,
           @pMAIN_2602032108437581183 AS Preferences
    UNION ALL
    SELECT @pMAIN_2602032108437581184 AS CustomerID,
           @pMAIN_2602032108437581185 AS Preferences
    UNION ALL
    SELECT @pMAIN_2602032108437581186 AS CustomerID,
           @pMAIN_2602032108437581187 AS Preferences)
SELECT CustomerID,
       Preferences,
       CASE WHEN Preferences & @pMAIN_2602032108437581188 = @pMAIN_2602032108437581189 THEN @pMAIN_260203210843758118_10 ELSE @pMAIN_260203210843758118_11 END AS PrefersEmail,
       CASE WHEN Preferences & @pMAIN_260203210843758118_12 = @pMAIN_260203210843758118_13 THEN @pMAIN_260203210843758118_14 ELSE @pMAIN_260203210843758118_15 END AS PrefersPhone,
       CASE WHEN Preferences ^ @pMAIN_260203210843758118_16 = @pMAIN_260203210843758118_17 THEN @pMAIN_260203210843758118_18 WHEN Preferences ^ @pMAIN_260203210843758118_19 = @pMAIN_260203210843758118_20 THEN @pMAIN_260203210843758118_21 WHEN Preferences ^ @pMAIN_260203210843758118_22 = @pMAIN_260203210843758118_23 THEN @pMAIN_260203210843758118_24 ELSE @pMAIN_260203210843758118_25 END AS PreferenceCategory
FROM CustomerPreferences;


            
        

Parameters (If used)

Query Results: