SQL Server - Modulo (%) Operator

The SQL Server (Transact-SQL) % (modulo) operator is used to calculate remainder of a division operation. It operates on numerical values.

The example below describes how to use modulo operator in various conditions:

Example:

Consider a database table called Sample with the following records:

• Using with WHERE Clause: To select records of table where Var1 column value is divisible by Var2 column value, the query is given below.

  SELECT * FROM Sample
  WHERE Var1 % Var2 == 0;
  

  
  

  The query will produce following result:

  Data	Var1	Var2
  Data1	10	1
  Data5	30	5

• Using with AS Clause: The value of Var1 % Var2 operation can be displayed in a different column using AS clause:

  SELECT *, (Var1 % Var2) AS Remainder FROM Sample;
  

  
  

  The query will produce following result:

  Data	Var1	Var2	Remainder
  Data1	10	1	0
  Data2	15	2	1
  Data3	20	3	2
  Data4	25	4	1
  Data5	30	5	0
  Data6	35	6	5

• Using with UPDATE Clause: To update the column Var1 with the value of Var1 % Var2 operation, the query is given below:

  UPDATE Sample
  SET Var1 = Var1 % Var2;
  
  --See result
  SELECT * FROM Sample;
  

  
  

  The query will produce following result:

  Data	Var1	Var2
  Data1	0	1
  Data2	1	2
  Data3	2	3
  Data4	1	4
  Data5	0	5
  Data6	5	6

• Using with values: To calculate the remainder of a division operation, we can simply use SELECT statement:

  SELECT 100 % 60;
  

  
  

  The query will produce following result:

  40
  
  (1 row(s) affected)