2

Could someone explain the difference between % in SQL?

I understand that % is a wildcard that allows you to query results with LIKE results, i.e. a% for words starting with a, but I am confused why the wildcard can be used as % 2 = 0 to query for even numbers?

I saw an explanation that said % can be used as divide but I thought / was divide.

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Josh Neal
  • 31
  • 1
  • Possible duplicate of [Understanding The Modulus Operator %](https://stackoverflow.com/questions/17524673/understanding-the-modulus-operator) – Igor Jun 12 '19 at 18:30
  • Which dbms are you using? (ANSI SQL has no such % usage...) – jarlh Jun 12 '19 at 20:43

3 Answers3

2

a % 2 = 0 here % as Modulus arithmetic operator.

Syntax: dividend % divisor

Sample: SELECT 15 % 2 AS Remainder it will return the result as 1

Demo on db<>fiddle

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
2

When used outside of a string, the percentage symbol % is the modulus operator, i.e. an operator which returns the remainder following division of the number preceding the operator by that following it.

Therefore, in your example, the expression % 2 = 0 will be validated if the number preceding the percentage symbol is even, e.g. 12 % 2 = 0 will return True.

Whereas, when used in the pattern argument of a like expression, the percentage symbol represents a wildcard operator matching any sequence of characters (or no characters at all).

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
0

Let's understand with an example:
I have created an Table name - 'c', which contain 2 attribute 'name' and 'num'.

when num%10 is calculated e.g. 55%10 -> gives 5.
If result is either 2 or 7 then it will not print that row
Elseif result (num%10) is NOT 2 or 7 then in this case it will print the row.

Now:

Select *from c where num%10 NOT In(2,7);

Check out Screenshot here :enter image description here

Kvyas
  • 21
  • 6