Geek Answers Handbook

How to generate ten absolute random numbers in SQL Server?

How to create ten absolute random numbers in SQL Server?

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

but I can’t control the number of digits!

+4
source share
3 answers

RAND () returns a number from 0 to 1. Therefore, you do not need a call for ABS (). You can get a 10-digit random integer by multiplying the RAND result by 10 by the power of 10 (1,000,000,000) and then rounding the result (I selected the floor in the example below, but you can use CEILING () or ROUND ()), since 10 digits are at the limit of the int data type, I throw POWER () 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

+6
source

You can make random numbers fall into the range so 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() always < 0 => (99000-10000+1)*RAND() always [0,89,001) , so you will get a random number from 10,000 to 89,000, all of which have 5 digits.

Other methods for random numbers in general are here.

+3
source

it 10 random numbers =)

[NOTE: I'm weird ... I know]

 SELECT CAST((1/rand()*1000) AS INT) % 10 AS One, CAST((1/rand()*1000) AS INT) % 10 AS Two, CAST((1/rand()*1000) AS INT) % 10 AS Three, CAST((1/rand()*1000) AS INT) % 10 AS Four, CAST((1/rand()*1000) AS INT) % 10 AS Five, CAST((1/rand()*1000) AS INT) % 10 AS Six, CAST((1/rand()*1000) AS INT) % 10 AS Seven, CAST((1/rand()*1000) AS INT) % 10 AS Eight, CAST((1/rand()*1000) AS INT) % 10 AS Nine, CAST((1/rand()*1000) AS INT) % 10 AS Ten

Results:

 One Two Three Four Five Six Seven Eight Nine Ten 6 0 1 2 1 0 1 5 2 5
+1
source

More articles:


All Articles