What does % in this WHERE clause do?

It is being used as a Modulo Operator; returning the remainder of a number divided by another.

In your example, the WHERE clause is limiting the results to only those where the Col1 value divided by 3 leaves a remainder of 1. (e.g. 4,7,10, etc.)


It's the Modulo operator.

Returns the remainder of one number divided by another.


% in SQL can be used in two different formats. If it's used in a string with LIKE then it's a wild card, for example:

WHERE col_name like '%test%'

That would mean find everything in col_name which has the word "test" in it.

But in this specific operation the % symbol is being used as a modulo operator. Modulo (on a calculator normally shown as MOD button) returns the remainder. It can be quite a useful tool if using a for loop to display data and you want to count columns - I have recently used the modulo operator when drawing up a web page. Mine was in PHP, but this is an example:

$count = 0 ;
$records = array () ;
for ( $a = 0 ; $a < 100 ; $a++ )
    $records[$a] = $a ;
foreach ( $records as $record )
{
    if ( $count % 3 == 0 )
        echo '<p>Three items</p>' ;
    if ( $count % 10 == 0 )
        echo '<p>Ten items</p>' ;
    echo '<p>'.$record.'</p>' ;
    $count++ ;
}

This would basically output 1 - 100 and every three items it would output "Three items", and every ten items it would output "Ten Items" because any other numbers would return a value e.g.:

5 / 10 = 0.5 (5 % 10 = 5)
2 / 3 = 0.666... (2 % 3 = 2)
50 / 10 = 5.0 (50 % 10 = 0)
9 / 3 = 3.0 (9 % 3 = 0)