SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

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:

DataVar1Var2
Data1101
Data2152
Data3203
Data4254
Data5305
Data6356

  • 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:

    DataVar1Var2
    Data1101
    Data5305
  • 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:

    DataVar1Var2Remainder
    Data11010
    Data21521
    Data32032
    Data42541
    Data53050
    Data63565
  • 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:

    DataVar1Var2
    Data101
    Data212
    Data323
    Data414
    Data505
    Data656
  • 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) 
    

❮ SQL Server Operators