# Generate random int value from 3 to 6

1 view
in SQL

Is it possible in Microsoft SQL Server generate random int value from Min to Max (3-9 example, 15-99 e.t.c)

I know, I can generate from 0 to Max, but how to increase Min border?

This query generates random value from 1 to 6. We need to change it from 3 to 6.

SELECT table_name, 1.0 + floor(6 * RAND(convert(varbinary, newid()))) magic_number

FROM information_schema.tables

Stupid question, sorry...

SELECT table_name, 3.0 + floor(4 * RAND(convert(varbinary, newid()))) magic_number

FROM information_schema.tables

by (40.3k points)

Below code will generate the random number between 0-9

ABS(CHECKSUM(NEWID()) % 10)

1 through 6

ABS(CHECKSUM(NEWID()) % 6) + 1

3 through 6

ABS(CHECKSUM(NEWID()) % 4) + 3

Dynamic (Based on Eilert Hjelmeseths Comment)

ABS(CHECKSUM(NEWID()) % (@max - @min + 1)) + @min

Note:

• NEWID can be used to generate a random string (for each row in return)
• Whereas, CHECKSUM takes a value of the string and creates a number
• modulus (%) divides by that number and returns the remainder (meaning the max value is one less than the number you use)
• ABS is used to change the negative results to positive then add one to the result to eliminate 0 results (to simulate a dice roll)