March 12, 2021 11:17 by
Peter
In this blog, we will learn about how to use RAND() function based on our business requirement.
So here we will cover the following things:
Definition
Random Decimal Number
Random Integer Range
Real example
Summary
Definition
As the name suggests the RAND function can be used to return any random number which can be decimal or integer.
The syntax for the RAND function would be:
SELECT RAND()
This function will return any random number like this image.
We can create any random decimal number between two given numbers, so for that, we can use this formula.
SELECT RAND()*(b-a)+a;
Here in this formula, you will use b for greater number and a for a lower number, so this formula will return a number between this.
Random Integer Range
We can create any random integer number between two given numbers, so for that, we can use this formula.
SELECT FLOOR(RAND()*(b-a+1))+a;
Here in this formula, you will use b for greater number and a for a lower number, so this formula will return a number between this.
Note
This RAND() function we can use on the following SQL version, SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.
Real example
Now let's see the real example on this function, Here I am using multiple examples so that you can differentiate among them.
--//---1- Random Decimal-----//
SELECT RAND()
SELECT RAND(7);
SELECT RAND(-7);
SELECT RAND()*(7-1)+1;
SELECT RAND(8)*(7-1)+1;
SELECT RAND(-4)*(7-1)+1;
--//---2- Random Integer-----//
SELECT FLOOR(RAND()*(8-4+1))+4;
SELECT FLOOR(RAND(6)*(8-5+1))+5;
SELECT FLOOR(RAND(123456789)*(10-5+1))+5;
See this image for result,
HostForLIFEASP.NET SQL Server 2019 Hosting