Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server 2012 Management Studio Keyboard Shortcuts - Cut and Paste
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

The modulo (%) operator returns the remainder of one number divided by another. The syntax of the modulo (%) operator is as follows:

<dividend> % <divisor>

The <dividend> is the numeric expression to divide and it must be a valid expression of any one of the data types in the integer and monetary data type categories, or of the numeric data type.  The <divisor> is the numeric expression to divide the dividend by and must be a valid expression of any one of the data types in the integer and monetary data type categories, or of the numeric data type.

Here are a few examples on the uses of the modulo (%) operator:

Usage #1 : Generate a Random Number

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1 AS [RandomNumber]

Usage #2 : Determine if a Number is Odd or Even

DECLARE @Input INT
SET @Input = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1
SELECT @Input AS [Input], CASE WHEN @Input % 2 = 0 THEN 'Even' ELSE 'Odd' END

Usage #3 : Determine if a Date/Year is a Leap Year

DECLARE @Year INT = YEAR(GETDATE())
SELECT CASE WHEN (@Year % 4 = 0 AND @Year % 100 != 0) OR @Year % 400 = 0
            THEN 'Leap Year'
            ELSE 'Not a Leap Year'
       END AS [IsLeapYear]

Usage #4 : Determine if a Number is a Palindrome

DECLARE @Input INT = 123454321
DECLARE @ReversedInput INT = 0
DECLARE @TempInput INT
SET @TempInput = @Input
WHILE @TempInput > 0
BEGIN
    SET @ReversedInput = @ReversedInput * 10 + @TempInput % 10
    SET @TempInput = @TempInput / 10
END
SELECT @Input AS [Input], CASE WHEN @Input = @ReversedInput
                               THEN 'Palindrome'
                               ELSE 'Not a Palindrome' END AS [Palindrome]

Usage #5 : Get the Greatest Common Factor (GCF)

DECLARE @Dividend			INT = 1989
DECLARE @Divisor			INT = 867
DECLARE @Remainder			INT = -1

WHILE @Remainder != 0
BEGIN
    SET @Remainder = @Dividend % @Divisor
    SET @Dividend = @Divisor
    SET @Divisor = @Remainder
END

SELECT @Dividend AS [GCF]

GCF
-----
51

Usage #6 : Determine if a Number is a Prime Number

DECLARE @Input			INT
DECLARE @IsPrime		BIT = 1
DECLARE @Factor			INT = 2

SET @Input = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 3
WHILE @Factor <= SQRT(@Input)
BEGIN
    IF @Input % @Factor = 0
    BEGIN
        SET @IsPrime = 0
        BREAK
    END

    SET @Factor = @Factor + 1
END

SELECT @Input AS [Input], @IsPrime AS [IsPrime]

Usage #7 : Determine if a Number is Divisible by Another Number

DECLARE @Dividend		INT
DECLARE @Divisor		INT

SET @Dividend = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 1000 + 100
SET @Divisor  = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 10 + 1
SELECT @Dividend AS [Dividend], @Divisor AS [Divisor],
       CASE WHEN @Dividend % @Divisor = 0
            THEN 'Divisible'
            ELSE 'Not Divisible'
       END

Back to Tip of the Day List Next Tip