Modulo: Negative Values Keep the Dividend Sign

Modulo returns a remainder, which can be negative when the dividend is negative. I distinguish that signed result from a bucket number that must stay within a positive range.

Decide which remainder contract is needed

The percent operator returns the remainder of division. It is an arithmetic operator in this expression. A percent character used inside a LIKE pattern answers another question. The surrounding SQL determines which meaning applies.

A signed remainder can be appropriate when reconstructing a signed integer division. A repeating bucket often needs a nonnegative position instead. Those are different contracts over the same number. I’d name the required interval before selecting an expression.

This example uses a fixed positive divisor of seven. That deliberately excludes zero and negative divisors from the bucket rule. The result interval is zero through six. A general reusable routine would need to validate its own divisor contract.

Keep raw and normalized remainders together

The inputs include negative eight, negative seven, negative one, zero, one, seven and eight. A final row contains NULL. Each output keeps the original integer and its raw remainder. Another expression normalizes that remainder into the chosen interval.

The normalization adds seven to the raw remainder and applies modulo again. Negative one therefore becomes six. Negative eight also becomes six because its raw remainder is negative one. A multiple of seven remains zero rather than becoming seven.

Keeping both outputs makes the policy visible. A negative result isn’t automatically a broken modulo calculation. It is the signed remainder expected from that input. The normalized column performs an additional transformation for a different consumer.

WITH Inputs AS
(
    SELECT Id, InputValue
    FROM (VALUES (1, -8), (2, -7), (3, -1), (4, 0),
                 (5, 1), (6, 7), (7, 8), (8, NULL)) AS v(Id, InputValue)
)
SELECT Id, InputValue, InputValue % 7 AS SignedRemainder,
       ((InputValue % 7) + 7) % 7 AS PositiveBucket
FROM Inputs
ORDER BY Id;

Check the range without inventing a new meaning

The second modulo step keeps an already nonnegative remainder within the same interval. Simply adding seven would turn a raw zero into seven. That would violate a zero-based bucket contract. The full expression matters, even when one negative example looks correct.

I can justify a one-based label for a human-facing position. That would add another explicit transformation after the zero-based result. Don’t quietly change the arithmetic and label together. The input, intermediate remainder and final position should remain understandable.

The constants are small enough to avoid intermediate overflow here. A generalized expression adding a large divisor needs a numeric range review. Changing the input to bigint can require a matching output contract. This article demonstrates only the stated integer range.

Retain zero and missing input

Zero is a real input belonging to bucket zero. NULL remains unknown in both expressions. Replacing NULL with zero would assign a position without an original value. The script makes no such default.

All rows come from a literal VALUES list in a CTE. The final SELECT changes no objects or connection state. ORDER BY fixes the eight-case sequence. Complete inputs and both outputs remain available for comparison.

Compare all eight ordered integer tuples, including every negative case and both exact multiples. Keep the raw remainder beside its normalized counterpart. Check the mapped value as well as its range. A range check alone could miss an incorrect mapping within that range.

Write the bucket rule down once, and negative numbers stop being scary.

A signed remainder is not a positive position, it is a step that needs normalizing.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.