Compound Operators in SQL Server

With Compound Operators, we can make operations on variables more easily. For example, let’s assume that @a is a variable and we want to increase the value of @ a by 2. Under normal conditions, we can increase the value of @a by 2 with the process “@ a = @ a + 2”.

With the “+ =” which is one of the compound operators, we can perform the same operation with @ a + = 2 in a simpler way.

Below is a list of compound operators.

Operator Explanation
+= We can use @a+=2 instead of @[email protected]+2
-= We can use @a-=2 instead of @[email protected]
*= We can use @a*=2 instead of @[email protected]*2

 

For Example;

Declare @a int

SET @a=3;

SET @a*=3

Select @a

 

/= We can use @a/=2 instead of @[email protected]/2
%= We can use @a%=2 instead of @[email protected]%2.

 

The % sign returns the remainder as a result when you divide a number by the other number.

 

For example, suppose that the value of @a is initially 9. As a result of “@a%=4”, the value of @a will be 1. Because if you divide 9 by 4, the remainder will be 1.

&= Performs a “bitwise AND” operation. For example;

 

Declare @myvariable int

Set @myvariable = 170

Set @myvariable &=75

Select @myvariable as Result

 

The binary value of the value 170 in the query is “0000 0000 1010 1010” and the binary value of 75 is “0000 0000 0100 1011”.

 

When you perform “AND” operation on these two binary values, the result will be 10. The AND operation is the result of the multiplication of both binary values. That is, if the two corresponding values ​​are 1, the result will be 1. If any is zero, the result will be 0.

^= Performs a “bitwise Exclusive OR”. For example;

 

Declare @myvariable int

Set @myvariable = 170

Set @myvariable ^=75

Select @myvariable as Result

 

The binary value of 170 in the query is “0000 0000 1010 1010”. The binary value of 75 is “0000 0000 0100 1011”.  When you perform “exlusive OR” operation on these two binary values, the result will be “0000 0000 1110 0001”. That is, if one of the two corresponding values ​​are 1, the result will be 1. If both of them are 1, the result will be 0. When you run the query, the result will return 225. Because the decimal value of “0000 0000 1110 0001” is 225.

|= Performs a “bitwise OR”. For example;

 

Declare @myvariable int

Set @myvariable = 170

Set @myvariable |=75

Select @myvariable as Result

 

The binary value of 170 in the query is “0000 0000 1010 1010”. The binary value of 75 is “0000 0000 0100 1011”.  When you perform “OR” operation on these two binary values, the result will be “0000 0000 1110 1011”.  That is, if one of the two corresponding values ​​are 1, the result will be 1.  When you run the query, the result will return 235. Because the decimal value of “0000 0000 1110 1011” is 235.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *