Menu
I have to generate 12 digit number(bigint) in sql server 2008 R2 itsleft. i have used these methods which i found on the internet
convert(numeric(12,0),rand() * 999999999999)
and
RIGHT(CAST(CAST(NEWID() AS VARBINARY(36)) AS BIGINT), 12)
while these do work the problem is sometimes the generated number has leading zeroes and that is stripped resulting in a 10 or 11 digit number.
Is there a consistent way to generate x digit numbers in sql?
Thanks. this is very urgent.
522
There are several methods to generate a random number. One simple method is to make use of RAND() function available in SQL Server. RAND() function simply generate a decimal number between 0 (inclusive) and 1 (exclusive).
for example 0987654321 note that each digit appear once.. it generates but at some point numbers repeat. but it is client need to make sure in 9 digit sequence no number appears twice.. You are not looking for a random number then as it is perfectly valid for a random number generator to repeat the same value.
SQL Server RAND() Function The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).
SQL Server has a built-in function that generates a random number, the RAND() mathematical function. The RAND math function returns a random float value from 0 through 1. It can take an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.
One solution is to just make sure it's in the 12 digit range.
convert(numeric(12,0),rand() * 899999999999) + 100000000000
How about add a random number to 100000000000
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
