SQL Server RAND() function | Generating random number in a range
In this article, we will see RAND() function and also see how to generate a
random number in a range using RAND() function.
RAND() Function
The RAND function can be used to return a random number. It will return a
value between 0 and 1 such as value greater than 0 and less than 1 ( 0>
value <1). By default, this function will return a completely random
number, if the seed value is provided then a repeatable sequence of the random
number will be returned.
Syntax: RAND([seed])
Return Type: Float
Examples
SELECT RAND() -- Result - 0.182458908613686
select RAND(10) -- Result - 0.713759689954247
select RAND(-10) -- Result - 0.713759689954247
Generating Random Number within the specific range
In development, we often need to generate a random number, sometimes random
number in a range e.g. 1 to 1000.
Random Decimal Range
To create a random decimal number between two values (range), you can use the
following formula:
SELECT (MAX_VAL-MIN_VAL) * RAND() + MIN_VAL
Where MAX_VAL is the maximum/upper/highest value and MIN_VAL is the
minimum/lower/smallest value that you want to generate a random in a range
Random Integer Range
To create a random integer number between two values (range), you can use
the following formula:
SELECT ROUND((MAX_VAL-MIN_VAL) * RAND() + MIN_VAL,0)
OR
SELECT FLOOR((MAX_VAL-MIN_VAL) * RAND() + MIN_VAL)
Where MAX_VAL is the maximum/upper/highest value and MIN_VAL is the minimum/lower/smallest value that you want to generate a random in a range.
Both methods will results the same output.
Examples
Let us see the queries to return a random number between 2000 and 3000.
DECLARE @MIN_VALUE INT = 2000
DECLARE @MAX_VALUE INT = 3000
-- Random Decimal Range
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND() + @MIN_VALUE
-- RESULT 2182.45890861369
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND(10) + @MIN_VALUE
-- RESULT 2713.75968995425
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND(-10) + @MIN_VALUE
-- RESULT 2713.75968995425
-- Random Integer Range
SELECT ROUND(((@MAX_VALUE - @MIN_VALUE) * RAND() + @MIN_VALUE), 0)
-- RESULT 2182
SELECT FLOOR(((@MAX_VALUE - @MIN_VALUE) * RAND(100) + @MIN_VALUE))
-- RESULT 2715
SELECT FLOOR(((@MAX_VALUE - @MIN_VALUE) * RAND(12345) + @MIN_VALUE))
-- RESULT 2943
Thanks for your time. If you have any suggestions or queries, please comment below.
Hi there to everybody, it’s my first go to see of this web site; this weblog consists of awesome and in fact good stuff for visitors. Hurrah, that’s what I was exploring for, what stuff! Existing here at this blog, thanks admin of this web site. You can also visit alteryx certification questions and answers for more Analytics Fun related information and knowledge.
ReplyDeleteThis information is meaningful and magnificent which you have shared here about the Truck Tuning. I am impressed by the details that you have shared in this post and It reveals how nicely you understand this subject. I would like to thanks for sharing this article here.Custom Truck Tuning
ReplyDelete