0 votes
1 view
in SQL by (20.3k points)

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

Added 5 sec later:

Stupid question, sorry...

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

FROM information_schema.tables

1 Answer

0 votes
by (36.7k 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)
...