asked on

Rand() function for integer

Hi,

From
select Cast(rand() * 100 as int) + 1 as "1 to 100"

I don't understand why would I add 1 to Cast(rand() * 100 as int).
Can't 'Cast(rand() * 100 as int)' itself generate an integer between 1 and 100?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lokesh B R

Hi,

The output of the RAND function will always be a value between 0 and 1.
If you want to generate a random integer number, all you have to do is multiply it by the maximum value you want generated and then get rid of the decimal places. One way of getting rid of the decimal places is by CAST it to INT.

Here's an example of generating a random number with a maximum value of 999,999:

SELECT CAST(RAND() * 100 AS INT) AS [RandomNumber]

SELECT CAST(RAND() * 1000 AS INT) AS [RandomNumber]

SELECT CAST(RAND() * 10000 AS INT) AS [RandomNumber]

See more information here
http://www.sql-server-helper.com/tips/generate-random-numbers.aspx

Jim Horn

For some more examples, I have an article on SQL Server: T-SQL recipe to create a million sample people which uses RAN() quite a bit to generate numbers for phone, zip, SSN, house numbers, and random letters.

Scott Pletcher

I'm not sure adding 1 is technically a safe way to do that, since I think it might be possible that the * 100 value cast to an int would already round up to 100, and when you add 1 you could get 101. I haven't tested that, but logically it seems possible to me, since CAST does indeed round the value.

I believe it would be safer to use:

CAST(CEILING(RAND() * 100) AS int)

The CAST is still necessary because the CEILING will return a float as it retains the original data type, but the CEILING will have done all the rounding, the cast just converts it to an int data type.