CODEWITHSUNDEEP
sql-server
HAJJAJ
HAJJAJ

Reputation: 3777

How to generate ten absolute Random digit in SQL Server?

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!!

Upvotes: 4

Views: 10097

Answers (3)

Josh Darnell
Josh Darnell

Reputation: 11433

This is 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

Upvotes: 2

Adrian Toman
Adrian Toman

Reputation: 11486

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

Upvotes: 6

Icarus
Icarus

Reputation: 63966

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.

Upvotes: 3

Related Questions