How to generate ten absolute Random digit in SQL Server?

Question

How to generate ten absolute Random digit in SQL Server?

I tried select ABS(CHECKSUM(rand()))

but I can't control the numbers of digits!!

Answer 1

RAND() returns a number between 0 and 1. So you don't require a call to ABS(). You can get a 10 digit random integer by multiplying the result of RAND by 10 to the power of 10 (10000000000) and then rounding the result (I have choosen floor in the example below but you could use CEILING() or ROUND()). Since 10 digits is at the limit of the int data type I'm casting POWER()'s as bigint.

SELECT FLOOR(RAND() * POWER(CAST(10 as BIGINT), 10))

References

http://msdn.microsoft.com/en-us/library/ms177610.aspx
http://msdn.microsoft.com/en-us/library/ms187745.aspx

Answer 2

You can force the random numbers to fall within a range such that they have the same number of digits, for example:

SELECT 10000 + CONVERT(INT, (99000-10000+1)*RAND())


SELECT len(CAST(10000 + CONVERT(INT, (99000-10000+1)*RAND()) as VARchar(20))) --5 digits

Because rand() is always < 0 => (99000-10000+1)*RAND() is always [0,89,001) therefore you'll end up with a random number between 10,000 and 89,000 which all have 5 digits.

Other techniques for random numbers in general are here.