What's the Percent Character (%) in SQL?

% - the percent character in SQL is the modulo (aka modulus) operator, which means give the remainder of a division. For example, 11 divided by 4 is 2 with a remainder of 3. It's the 3 that we're interested in.

I think about the operation in a few different ways. Depending on what form your numbers are in and what functions are available to you, or depending on which strikes you as the most natural, one of these might be more helpful to you than the others.

Form 1 - modulo is to subtract from n the highest multiple of x that is less than n:

n % x = n – floor(n / x)

Form 2 - modulo is to divide n by x, remove the whole number portion, then multiply by x again:

n % x = (n / x - floor(n / x)) * x

Form 3 - modulo is, using a clock with x numbers (from 0 to x - 1), to start on 0 and take n steps, then look at what number we end up on.

Operation: 11 % 4
Okay. Here's a 4-numbered clock to count on:

  0
3   1
  2

Starting at 0 we take 11 clockwise steps and see that we end up on 3.

So 11 mod 4 = 3.

Here are some syntax notes for other programming environments:

n mod x <- how it’s written in VB
mod(n, x) <- how it’s written in Excel

It’s a nice way to get the remainder without having to use n twice in an expression.

Examples:

10 % 10 = 0
10 % 9 = 1
10 % 8 = 2
10 % 7 = 3
10 % 6 = 4
10 % 5 = 0

It helps to keep in mind that the result will always be in the range 0 to (x – 1). Anything mod 10 will always end up in the range 0 to 9. Modulo has the same precedence as multiply and divide (at least in SQL): lower than negation and bitwise not but higher than everything else.

If you want to add 1 to an hour value of 0 to 23 and not just end up with 1 – 24, modulo is a great way to do that.

For an hour value that goes from 0 to 23, to add one hour:

(Hr + 1) % 24 -> 0 to 23 -> 1 to 24 -> 1 to 23, 0 (0 to 23 again, rotated up one)

For an hour value that goes from 1 to 24, to add one hour:

Hr % 24 + 1 -> 1 to 24 -> 1 to 23, 0 -> 2 to 24, 1 (1 to 24 again, rotated up one)

Since the modulo of negative numbers can give unexpected results depending on the programming language (-1 % x could be 1 or it could be x – 1, e.g., -1 mod 5 could be 1 or 4) you just add a multiple of x (since that won't change the remainder but will get us out of negative territory), then subtract what you need. Usually you only need x.

Here’s an example of finding last month’s month number from this month’s month number. Note that when you have ranges that start with 1 instead of 0, sometimes you have to subtract 1 before the modulo and then add 1 after the modulo.

Note: we’re subtracting one from the month, then adding 12 so we don’t get negative numbers. We also subtract 1 before the modulo and add 1 after the modulo (to convert the 1-based range to a 0-based range and back).

(Month – 1 + 12 - 1) % 12 + 1
which simplified is:
(Month + 10) % 12 + 1 -> 1 to 12 -> 11 to 22 -> 11, 0 to 10 -> 12, 1 to 11