SQL Server logical bit operation example

I. Scenario: In some scenarios, a series of bit types need to be saved. To simplify the database structure or other special requirements, you can save eight bit types as one byte (for example, tinyint type ). Ii. Logical bit operation functions: (1) "logical bit and ". When and only when the values of the two bits in the input expression (the current bit being parsed) are 1

I. Scenario: In some scenarios, a series of bit types need to be saved. To simplify the database structure or other special requirements, you can save eight bit types as one byte (for example, tinyint type ). Ii. Logical bit operation functions: (1) "logical bit and ". When and only when the values of the two bits in the input expression (the current bit being parsed) are 1

I. scenarios:

In some scenarios, you need to save a series of bit types. To simplify the database structure or other special requirements, you can save eight bit types as one byte (for example, tinyint type ).

Ii. Logical bit operation functions:

(1)"Logical bit and ".When and only when the values of the two bits in the input expression (the current bit being parsed) are both 1, the bits in the result are set to 1; otherwise, the bit in the result is set to 0.

Example: select 170 & 75

1010 1010 <-- 170
0100 1011 <-- 75
-------------------
0000 1010 <-- 10

(2)"Logical bit or". If one or both bits in the input expression are 1 (for the current bit being parsed), the bit in the result is set to 1; if neither of the two bits in the input expression is 1, the bits in the result will be set to 0.

Example: select 170 | 75

1010 1010 <-- 170
0100 1011 <-- 75
-------------------
1110 1011 <-- 235

(3)"Logical bit exclusive or". If the values of the corresponding two bits are both 0 or 1, the value of this bits in the result is cleared to 0; otherwise (only one of the two corresponding bits is 1), and the value of this bits in the result is set to 1.

Example: select 170 ^ 75

1010 1010 <-- 170
0100 1011 <-- 75
-------------------
1110 0001 <-- 225

3. Read and judge:

For example, if (170 & 8) = 8 is used to determine whether the right number of 4th bits is 1. That is, you only need to perform the logical bit and operation on this bit to shield the other seven digits.

1010 1010 <-- 170
0000 1000 <-- 8

-------------------

0000 1000 <-- 8

Custom functions:

Create function dbo. JudgeBit (@ CombinedValue Tinyint, @ BitPosition Tinyint) RETURNS Bit AS BEGIN RETURN @ combinedValue & POWER (2, @ BitPosition-1) END

-- @ CombinedValue Original Value (10 hexadecimal), for example: 170

-- @ BitPosition: number of digits, for example, 4

-- The return value is 0 or 1.

Iv. assignment:

(1) set a person to 1

You only need to perform the "logical bit or" operation on this "bit", that is, select x | 8

For example:

1010 1010 <-- 170
0000 0100 <-- 4

-------------------
1010 1110 <-- 174

(2) set a value to 0.

First, determine whether the bit is 0. if the current value is 1, perform the logic bitwise XOR operation on the bit, that is, if (x & 8) = 8 select x ^ 8

For example:

1010 1010 <-- 170

0000 1000 <-- 8

-------------------

1010 0010 <-- 162

Custom functions:

Create function SetBitValue (@ CombinedValue Tinyint, @ BitPosition Tinyint, @ BitValue Bit) RETURNS Tinyint AS BEGIN DECLARE @ ReturnValue Tinyint IF @ BitValue = 1 BEGIN Select @ ReturnValue = @ combinedValue | POWER (2, @ BitPosition-1) END ELSE BEGIN IF @ combinedValue & POWER (2, @ BitPosition-1) = @ BitValue Select @ ReturnValue = @ combinedValue ELSE Select @ ReturnValue = @ combinedValue ^ POWER (2, @ BitPosition-1) END RETURN @ ReturnValue END

-- @ CombinedValue Original Value (10 hexadecimal), for example: 170

-- @ BitPosition: number of digits, for example, 4

-- @ BitValue value, which can only be 0 or 1

-- Return value (decimal), for example: 170

Tip:

SQL Server 2008 provides a value assignment function similar to the C language, such as "& =" (bit and equal), "| =" (at equal), "^ =" (bitwise OR equal ).