Tip of the Day : Example Uses of the RIGHT String Function
 SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day
 Example Uses of the Modulo (%) Operator

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