MOD

Edit online

The MOD function divides the first argument by the second argument and returns the remainder.



The formula used to calculate the remainder is:

MOD(x,y) = x - FLOOR(x/y) * y

Where x/y is the truncated integer result of the division. The result is negative only if the first argument is negative.

Each argument must be an expression that returns a value of any built-in numeric data type.

The arguments can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34).

The attributes of the result are based on the arguments as follows:

  • If both arguments are large or small integers, the data type of the result is large integer.
  • If both arguments are integers and at least one argument is a big integer, the data type of the result is big integer.
  • If one argument is an integer and the other is a decimal, the data type of the result is decimal with the same precision and scale as the decimal argument.
  • If both arguments are decimal, the data type of the result is decimal. The precision of the result is min(p-s,p'-s') + max(s,s') and the scale of the result is max(s,s'),
    • where the symbols p and s denote the precision and scale of the first argument, and the symbols p' and s' denote the precision and scale of the second argument.
    • If one argument is a floating-point number, and the other is not a DECFLOAT, or both argument is a floating-point number, the data type of the result is double precision floating-point.

      The operation is performed in floating-point. If necessary, the operands are first converted to double precision floating-point numbers. For example, an operation that involves a floating-point number and either an integer or a decimal number is performed with a temporary copy of the integer or decimal number that has been converted to double precision floating-point. The result of a floating-point operation must be within the range of floating-point numbers.

    • If either argument is a DECFLOAT, the data type of the result is DECFLOAT(34).

      If either argument is a special decimal floating point value, the general rules for arithmetic operations apply.

      If one argument is a DECFLOAT and the second argument is zero, the result is NaN and an invalid operation condition is returned.

Example:

The following example returns the remainder of dividing the value available in the SALARY column from the table EMPLOYEE by 3.


SELECT MOD(SALARY, 3) FROM EMPLOYEE

The above example returns the following.


2
2

1
0
0