SQL RAND() Function

The SQL RAND() function is used to generate a floating-point number or whole integer number by randomly.

The SQL RAND() function will generate unique(maximum time) number or duplicate(some time) number at every execution or function calling.

The SQL RAND() function will generate number between 0.0(inclusive) to 1.0(exclusive). The generated number will be in floating-point numbers and by default it will not generate whole integer number. We can generate whole interger number (from any number or within range between 2 numbers) using some formula's with rand() function.

It can be used in any valid SQL SELECT statement as well in SQL where clause.

You can also search these topics using sql random function, sql server random string, sql generate random string, sql random string, tsql random number, postgresql order by random, sql random select, sql order by random.

SQL RAND() Syntax

The below syntax is used to generate a random number (floating-point number).

For SQL SERVER / MYSQL


SELECT RAND(seed_number)

Note: The parameter "seed_number" is "OPTIONAL" and used to generate same number in the result.

For MS ACCESS


SELECT RND(seed_number)

The below syntax is used to generate a whole integer number (any numbers or within range of between 2 integer numbers).

For SQL SERVER / MYSQL


SELECT FLOOR((RAND() * (X - Y)) + Y)

For MS ACCESS


SELECT INT((RND() * (X - Y) + 1) + Y)

Note: Where "X" is the highest number and "Y" is the smallest number that you want to generate a random number between "X" and "Y".

SQL RAND() Example - Using Expression Or Formula

The following SQL SELECT statement return a random floating-point number between "0.0" and "1.0".


SELECT RAND()

The result of above query is:

Expr
0.38475628374

SQL RAND() Function More Example

Input Value	Result
RAND()	0.21342256
RAND()	0.82364526
RAND()	0.46338755
RAND(8)	0.33345321
RAND(8)	0.33345321
RAND(8)	0.33345321
FLOOR((RAND() * (20 - 10)) + 10)	13
FLOOR((RAND() * (20 - 10)) + 10)	18
FLOOR((RAND() * (20 - 10)) + 10)	11
FLOOR((RAND() * (50 - 30)) + 30)	42
FLOOR((RAND() * (50 - 30)) + 30)	39
FLOOR((RAND() * (50 - 30)) + 30)	31

Sample Database Table - Author

AuthorId	AuthorName	Gender	DomainName
1	Suresh Babu	Male	Hacking
2	Siva Kumar	Male	Database
3	Azagu Bala Haris	Male	Programming
4	Varshini Kutty	Female	Database
5	Bala Murugan	Male	Games
6	Dharan Kumar	Male	Games

SQL RAND() Example

There are 6 records in the "Author" table. So the maximum value will be "6" and the minimum value will be "1" in the condition.

The following SQL statement will fetch randomly any one record at a time from the "Author" table:


SELECT * FROM Author
WHERE AuthorID = FLOOR((RAND() * (6 - 1)) + 1)

Note: The RAND() function will generate any random number between "1" to "6". There will be only one record in the result.

The result of above query is for "First Time" run:

AuthorId	AuthorName	Gender	DomainName
3	Azagu Bala Haris	Male	Programming

The result of above query is for "Second Time" run:

AuthorId	AuthorName	Gender	DomainName
1	Suresh Babu	Male	Hacking

The result of above query is for "Third Time" run:

AuthorId	AuthorName	Gender	DomainName
6	Dharan Kumar	Male	Games

You can also search these topics using t sql generate random string, generate random number sql server, random sql data generator, sql random data generator, rand function sql, sql select random number, db2 random function, random number generator sql server, select random sql server, number functions in sql, sql server build numbers, sql string to number.