I'm having trouble replicating the mod function in SQL sever.
In excel, mod (-3, 7) = 4. But in SQL, -3 % 7 = -3
Am I using % wrong, or does SQL do mod differently?
Asked
Active
Viewed 5,494 times
2 Answers
31
This will give a result between 0 and n - 1 for both positive and negative values of x:
((x % n) + n) % n
Mark Byers
- 811,555
- 193
- 1,581
- 1,452
-
1My friend just pointed out that a shorter version would be (n + x) % n – Tom Halladay May 06 '12 at 18:31
-
14@TomHalladay: That works for x >= -n but will still give a negative result for x < -n. – Mark Byers May 06 '12 at 18:43
-
@MarkByers It depends, on Oracle I get the good result because the modulo implementation never returns less than -n... – Kiruahxh Oct 12 '20 at 08:09
2
Well, modular arithmetic is done on equivalence classes of integers, so neither Excel nor any RDBMS is "doing % wrong". If you want a representative between 0 and 6, though, you can always do
select (-3 % 7) + 7;
-
3
-
Wait, so you've got a pair of integers `m` and `n` such that `m` is congruent to 0 mod `n`, but `select m % n` is returning something other than zero? – May 06 '12 at 17:58
-
4@JackManey : Your answer in `(m % n) + n`, this gives `(0 % n) + n = n` where the OP wants it to equal `0`. That's a sub-set of the reasons why MarkByers' answer has an additional `% n`. – MatBailie May 06 '12 at 18:08