- Bitwise AND operator

Bitwise AND operator

1. Get Active Categories

SQL Server Query 1

            
-- Bit 1: 1 = Active, 0 = Inactive  

-- Bit 2: 1 = Seasonal, 0 = Non-seasonal  

-- Bit 3: 1 = Featured, 0 = Not featured  

--  1. Get Active Categories  

SELECT Categories.CategoryID, Categories.CategoryName  

  FROM Categories, (VALUES  

      (1, 3), -- 1 = CategoryID, 3 = BitFlag: Active and Seasonal (1 | 2 = 3)  

      (2, 5), -- 2 = CategoryID, 5 = BitFlag: Active and Featured (1 | 4 = 5)  

      (3, 0)  -- 3 = CategoryID, 0 = BitFlag: Inactive and Nonseasonal and Not Feature  

  ) AS CategoryFeature (CategoryID, BitFlag)  

WHERE Categories.CategoryID = CategoryFeature.CategoryID AND  

  (CategoryFeature.BitFlag & 1) = 1;  -- Bitwise AND with 1 (0001)

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

 .Select()  

  .Columns("Categories.CategoryID", "Categories.CategoryName")  

  .From(new Table("Categories"), new VALUES(new List<List<object>>()  

  { 

      new List<object>() {1, 3 },  

      new List<object>() {2, 5 },  

      new List<object>() {3, 0 },  

  }, "CategoryFeature", "CategoryID", "BitFlag"))  

  .Where(new Where(new Column("Categories.CategoryID").Equale(new Column("CategoryFeature.CategoryID")))  

      .AND(new ColumnArithmatic().StartBracket("CategoryFeature.BitFlag").BitwiseAND(1).EndBracket().Equale(1))  

  ) 

.Build();

Query build by SqlQueryBuilder 1

            
SELECT Categories.CategoryID,
       Categories.CategoryName
FROM Categories, (VALUES (@pMAIN_2608060423322094920, @pMAIN_2608060423322094921), (@pMAIN_2608060423322094922, @pMAIN_2608060423322094923), (@pMAIN_2608060423322094924, @pMAIN_2608060423322094925)) AS CategoryFeature(CategoryID, BitFlag)
WHERE Categories.CategoryID = CategoryFeature.CategoryID
      AND (CategoryFeature.BitFlag & @pMAIN_2608060423322094926) = @pMAIN_2608060423322094927;

Parameters (If used)

Name	Value
@pMAIN_2608060423322094920	1
@pMAIN_2608060423322094921	3
@pMAIN_2608060423322094922	2
@pMAIN_2608060423322094923	5
@pMAIN_2608060423322094924	3
@pMAIN_2608060423322094925	0
@pMAIN_2608060423322094926	1
@pMAIN_2608060423322094927	1

Query Results 1:

	CategoryID	CategoryName
1	1	Beverages
2	2	Condiments

2. Get Seasonal Categories

SQL Server Query 2

            
-- Bit 1: 1 = Active, 0 = Inactive  

-- Bit 2: 1 = Seasonal, 0 = Non-seasonal  

-- Bit 3: 1 = Featured, 0 = Not featured  

--  2. Get Seasonal Categories  

SELECT Categories.CategoryID, Categories.CategoryName  

FROM Categories, (VALUES  

(1, 3), -- 1 = CategoryID, 3 = BitFlag: Active and Seasonal (1 | 2 = 3)  

(2, 5), -- 2 = CategoryID, 5 = BitFlag: Active and Featured (1 | 4 = 5)  

(3, 0)  -- 3 = CategoryID, 0 = BitFlag: Inactive and Nonseasonal and Not Feature  

) AS CategoryFeature (CategoryID, BitFlag)  

WHERE Categories.CategoryID = CategoryFeature.CategoryID AND (CategoryFeature.BitFlag & 2) = 2;  -- Bitwise AND with 2 (0010)

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Columns("Categories.CategoryID", "Categories.CategoryName")  

    .From(new Table("Categories"), new VALUES(new List<List<object>>()  

    {  

        new List<object>() {1, 3 },  

        new List<object>() {2, 5 },  

        new List<object>() {3, 0 },  

    }, "CategoryFeature", "CategoryID", "BitFlag"))  

    .Where(new Where(new Column("Categories.CategoryID").Equale(new Column("CategoryFeature.CategoryID")))  

        .AND(new ColumnArithmatic().StartBracket("CategoryFeature.BitFlag").BitwiseAND(2).EndBracket().Equale(2))  

    )  

    .Build();

Query build by SqlQueryBuilder 2

            
SELECT Categories.CategoryID,
       Categories.CategoryName
FROM Categories, (VALUES (@pMAIN_2608060423322208990, @pMAIN_2608060423322208991), (@pMAIN_2608060423322208992, @pMAIN_2608060423322208993), (@pMAIN_2608060423322208994, @pMAIN_2608060423322208995)) AS CategoryFeature(CategoryID, BitFlag)
WHERE Categories.CategoryID = CategoryFeature.CategoryID
      AND (CategoryFeature.BitFlag & @pMAIN_2608060423322208996) = @pMAIN_2608060423322208997;

Parameters (If used)

Name	Value
@pMAIN_2608060423322208990	1
@pMAIN_2608060423322208991	3
@pMAIN_2608060423322208992	2
@pMAIN_2608060423322208993	5
@pMAIN_2608060423322208994	3
@pMAIN_2608060423322208995	0
@pMAIN_2608060423322208996	2
@pMAIN_2608060423322208997	2

Query Results 2:

	CategoryID	CategoryName
1	1	Beverages

3. Get Featured Categories

SQL Server Query 3

            
-- Bit 1: 1 = Active, 0 = Inactive  

-- Bit 2: 1 = Seasonal, 0 = Non-seasonal  

-- Bit 3: 1 = Featured, 0 = Not featured  

--  3. Get Featured Categories  

SELECT Categories.CategoryID, Categories.CategoryName  

FROM Categories, (VALUES  

(1, 3), -- 1 = CategoryID, 3 = BitFlag: Active and Seasonal (1 | 2 = 3)  

(2, 5), -- 2 = CategoryID, 5 = BitFlag: Active and Featured (1 | 4 = 5)  

(3, 0)  -- 3 = CategoryID, 0 = BitFlag: Inactive and Nonseasonal and Not Feature  

) AS CategoryFeature (CategoryID, BitFlag)  

WHERE Categories.CategoryID = CategoryFeature.CategoryID AND (CategoryFeature.BitFlag & 4) = 4;  -- Bitwise AND with 4 (0100)

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select()  

    .Columns("Categories.CategoryID", "Categories.CategoryName")  

    .From(new Table("Categories"), new VALUES(new List<List<object>>()  

    {  

        new List<object>() {1, 3 },  

        new List<object>() {2, 5 },  

        new List<object>() {3, 0 },  

    }, "CategoryFeature", "CategoryID", "BitFlag"))  

    .Where(new Where(new Column("Categories.CategoryID").Equale(new Column("CategoryFeature.CategoryID")))  

        .AND(new ColumnArithmatic().StartBracket("CategoryFeature.BitFlag").BitwiseAND(4).EndBracket().Equale(4))  

    )  

    .Build();

Query build by SqlQueryBuilder 3

            
SELECT Categories.CategoryID,
       Categories.CategoryName
FROM Categories, (VALUES (@pMAIN_2608060423322252710, @pMAIN_2608060423322252711), (@pMAIN_2608060423322252712, @pMAIN_2608060423322252713), (@pMAIN_2608060423322252714, @pMAIN_2608060423322252715)) AS CategoryFeature(CategoryID, BitFlag)
WHERE Categories.CategoryID = CategoryFeature.CategoryID
      AND (CategoryFeature.BitFlag & @pMAIN_2608060423322252716) = @pMAIN_2608060423322252717;

Parameters (If used)

Name	Value
@pMAIN_2608060423322252710	1
@pMAIN_2608060423322252711	3
@pMAIN_2608060423322252712	2
@pMAIN_2608060423322252713	5
@pMAIN_2608060423322252714	3
@pMAIN_2608060423322252715	0
@pMAIN_2608060423322252716	4
@pMAIN_2608060423322252717	4

Query Results 3:

	CategoryID	CategoryName
1	2	Condiments

4. Get Categories that are either Active or Featured

SQL Server Query 4

            
-- Bit 1: 1 = Active, 0 = Inactive  

-- Bit 2: 1 = Seasonal, 0 = Non-seasonal  

-- Bit 3: 1 = Featured, 0 = Not featured  

--  3. Get Featured Categories  

SELECT Categories.CategoryID, Categories.CategoryName  

FROM Categories, (VALUES  

(1, 3), -- 1 = CategoryID, 3 = BitFlag: Active and Seasonal (1 | 2 = 3)  

(2, 5), -- 2 = CategoryID, 5 = BitFlag: Active and Featured (1 | 4 = 5)  

(3, 0)  -- 3 = CategoryID, 0 = BitFlag: Inactive and Nonseasonal and Not Feature  

) AS CategoryFeature (CategoryID, BitFlag)  

WHERE Categories.CategoryID = CategoryFeature.CategoryID AND (CategoryFeature.BitFlag & (1 | 4 )) > 0;  -- Bitwise AND with (1 OR 4) = 5 (0101)

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .Select()  

    .Columns("Categories.CategoryID", "Categories.CategoryName")  

    .From(new Table("Categories"), new VALUES(new List<List<object>>()  

    {  

        new List<object>() {1, 3 },  

        new List<object>() {2, 5 },  

        new List<object>() {3, 0 },  

    }, "CategoryFeature", "CategoryID", "BitFlag"))  

    .Where(new Where(new Column("Categories.CategoryID").Equale(new Column("CategoryFeature.CategoryID")))  

        .AND(new ColumnArithmatic().StartBracket("CategoryFeature.BitFlag").BitwiseAND()  

            .StartBracket().Value(1).BitwiseOR(4).EndBracket()  

            .EndBracket().GreaterThan(0))  

    )  

    .Build();

Query build by SqlQueryBuilder 4

            
SELECT Categories.CategoryID,
       Categories.CategoryName
FROM Categories, (VALUES (@pMAIN_2608060423322282820, @pMAIN_2608060423322282821), (@pMAIN_2608060423322282822, @pMAIN_2608060423322282823), (@pMAIN_2608060423322282824, @pMAIN_2608060423322282825)) AS CategoryFeature(CategoryID, BitFlag)
WHERE Categories.CategoryID = CategoryFeature.CategoryID
      AND (CategoryFeature.BitFlag & (@pMAIN_2608060423322282826 | @pMAIN_2608060423322282827)) > @pMAIN_2608060423322282828;

Parameters (If used)

Name	Value
@pMAIN_2608060423322282820	1
@pMAIN_2608060423322282821	3
@pMAIN_2608060423322282822	2
@pMAIN_2608060423322282823	5
@pMAIN_2608060423322282824	3
@pMAIN_2608060423322282825	0
@pMAIN_2608060423322282826	1
@pMAIN_2608060423322282827	4
@pMAIN_2608060423322282828	0

Query Results 4:

	CategoryID	CategoryName
1	1	Beverages
2	2	Condiments

5. Get Categories that are Active AND Seasonal

SQL Server Query 5

            
-- Bit 1: 1 = Active, 0 = Inactive  

-- Bit 2: 1 = Seasonal, 0 = Non-seasonal  

-- Bit 3: 1 = Featured, 0 = Not featured  

--  3. Get Featured Categories  

SELECT Categories.CategoryID, Categories.CategoryName  

FROM Categories, (VALUES  

(1, 3), -- 1 = CategoryID, 3 = BitFlag: Active and Seasonal (1 | 2 = 3)  

(2, 5), -- 2 = CategoryID, 5 = BitFlag: Active and Featured (1 | 4 = 5)  

(3, 0)  -- 3 = CategoryID, 0 = BitFlag: Inactive and Nonseasonal and Not Feature  

) AS CategoryFeature (CategoryID, BitFlag)  

WHERE Categories.CategoryID = CategoryFeature.CategoryID AND (CategoryFeature.BitFlag & (1 | 2 )) = (1 | 2);

Create SQL query with SqlQueryBuilder 5

            
var (sql5, parameters5) = new SqlQueryBuilder()  

    .Select()  

    .Columns("Categories.CategoryID", "Categories.CategoryName")  

    .From(new Table("Categories"), new VALUES(new List<List<object>>()  

    {  

        new List<object>() {1, 3 },  

        new List<object>() {2, 5 },  

        new List<object>() {3, 0 },  

    }, "CategoryFeature", "CategoryID", "BitFlag"))  

    .Where(new Where(new Column("Categories.CategoryID").Equale(new Column("CategoryFeature.CategoryID")))  

        .AND(new ColumnArithmatic().StartBracket("CategoryFeature.BitFlag").BitwiseAND()  

            .StartBracket().Value(1).BitwiseOR(2).EndBracket()  

            .EndBracket().Equale(new ColumnArithmatic().StartBracket().Value(1).BitwiseOR(2).EndBracket()))  

    )  

    .Build();

Query build by SqlQueryBuilder 5

            
SELECT Categories.CategoryID,
       Categories.CategoryName
FROM Categories, (VALUES (@pMAIN_2608060423322311860, @pMAIN_2608060423322311861), (@pMAIN_2608060423322311862, @pMAIN_2608060423322311863), (@pMAIN_2608060423322311864, @pMAIN_2608060423322311865)) AS CategoryFeature(CategoryID, BitFlag)
WHERE Categories.CategoryID = CategoryFeature.CategoryID
      AND (CategoryFeature.BitFlag & (@pMAIN_2608060423322311866 | @pMAIN_2608060423322311867)) = (@pMAIN_2608060423322311868 | @pMAIN_2608060423322311869);

Parameters (If used)

Name	Value
@pMAIN_2608060423322311860	1
@pMAIN_2608060423322311861	3
@pMAIN_2608060423322311862	2
@pMAIN_2608060423322311863	5
@pMAIN_2608060423322311864	3
@pMAIN_2608060423322311865	0
@pMAIN_2608060423322311866	1
@pMAIN_2608060423322311867	2
@pMAIN_2608060423322311868	1
@pMAIN_2608060423322311869	2

Query Results 5:

	CategoryID	CategoryName
1	1	Beverages

6. Get Inactive Categories

SQL Server Query 6

            
-- Bit 1: 1 = Active, 0 = Inactive  

-- Bit 2: 1 = Seasonal, 0 = Non-seasonal  

-- Bit 3: 1 = Featured, 0 = Not featured  

--  3. Get Featured Categories  

SELECT Categories.CategoryID, Categories.CategoryName  

FROM Categories, (VALUES  

(1, 3), -- 1 = CategoryID, 3 = BitFlag: Active and Seasonal (1 | 2 = 3)  

(2, 5), -- 2 = CategoryID, 5 = BitFlag: Active and Featured (1 | 4 = 5)  

(3, 0)  -- 3 = CategoryID, 0 = BitFlag: Inactive and Nonseasonal and Not Feature  

) AS CategoryFeature (CategoryID, BitFlag)  

WHERE Categories.CategoryID = CategoryFeature.CategoryID AND (CategoryFeature.BitFlag & 1) = 0;

Create SQL query with SqlQueryBuilder 6

            
var (sql6, parameters6) = new SqlQueryBuilder()  

    .Select()  

    .Columns("Categories.CategoryID", "Categories.CategoryName")  

    .From(new Table("Categories"), new VALUES(new List<List<object>>()  

    {  

        new List<object>() {1, 3 },  

        new List<object>() {2, 5 },  

        new List<object>() {3, 0 },  

    }, "CategoryFeature", "CategoryID", "BitFlag"))  

    .Where(new Where(new Column("Categories.CategoryID").Equale(new Column("CategoryFeature.CategoryID")))  

        .AND(new ColumnArithmatic().StartBracket("CategoryFeature.BitFlag").BitwiseAND(1)                          

            .EndBracket().Equale(0))  

    )  

    .Build();

Query build by SqlQueryBuilder 6

            
SELECT Categories.CategoryID,
       Categories.CategoryName
FROM Categories, (VALUES (@pMAIN_2608060423322342360, @pMAIN_2608060423322342361), (@pMAIN_2608060423322342362, @pMAIN_2608060423322342363), (@pMAIN_2608060423322342364, @pMAIN_2608060423322342365)) AS CategoryFeature(CategoryID, BitFlag)
WHERE Categories.CategoryID = CategoryFeature.CategoryID
      AND (CategoryFeature.BitFlag & @pMAIN_2608060423322342366) = @pMAIN_2608060423322342367;

Parameters (If used)

Name	Value
@pMAIN_2608060423322342360	1
@pMAIN_2608060423322342361	3
@pMAIN_2608060423322342362	2
@pMAIN_2608060423322342363	5
@pMAIN_2608060423322342364	3
@pMAIN_2608060423322342365	0
@pMAIN_2608060423322342366	1
@pMAIN_2608060423322342367	0

Query Results 6:

	CategoryID	CategoryName
1	3	Confections