Is it possible in Microsoft SQL Server generate random int value from Min to Max (3-9 example, 15-99 e.t.c)
I know, i can generate from 0 to Max, but how to increase Min border?
This query generate random value from 1 to 6. Need to change it from 3 to 6.
SELECT table_name, 1.0 + floor(6 * RAND(convert(varbinary, newid()))) magic_number
FROM information_schema.tables
Added 5 sec later:
Stupid question, sorry...
SELECT table_name, 3.0 + floor(4 * RAND(convert(varbinary, newid()))) magic_number
FROM information_schema.tables
This question is related to
sql
sql-server
sql-server-2008
In general:
select rand()*(@upper-@lower)+@lower;
For your question:
select rand()*(6-3)+3;
<=>
select rand()*3+3;
DECLARE @min INT = 3;
DECLARE @max INT = 6;
SELECT @min + ROUND(RAND() * (@max - @min), 0);
Step by step
DECLARE @min INT = 3;
DECLARE @max INT = 6;
DECLARE @rand DECIMAL(19,4) = RAND();
DECLARE @difference INT = @max - @min;
DECLARE @chunk INT = ROUND(@rand * @difference, 0);
DECLARE @result INT = @min + @chunk;
SELECT @result;
Note that a user-defined function thus not allow the use of RAND(). A workaround for this (source: http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/) is to create a view first.
CREATE VIEW [dbo].[vw_RandomSeed]
AS
SELECT RAND() AS seed
and then create the random function
CREATE FUNCTION udf_RandomNumberBetween
(
@min INT,
@max INT
)
RETURNS INT
AS
BEGIN
RETURN @min + ROUND((SELECT TOP 1 seed FROM vw_RandomSeed) * (@max - @min), 0);
END
SELECT ROUND((6 - 3 * RAND()), 0)
Lamak's answer as a function:
-- Create RANDBETWEEN function
-- Usage: SELECT dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID())))
CREATE FUNCTION dbo.RANDBETWEEN(@minval TINYINT, @maxval TINYINT, @random NUMERIC(18,10))
RETURNS TINYINT
AS
BEGIN
RETURN (SELECT CAST(((@maxval + 1) - @minval) * @random + @minval AS TINYINT))
END
GO
I see you have added an answer to your question in SQL Server 2008 you can also do
SELECT 3 + CRYPT_GEN_RANDOM(1) % 4 /*Random number between 3 and 6*/
FROM ...
A couple of disadvantages of this method are
NEWID()
method but just thought I'd add it as another option.
Simply:
DECLARE @MIN INT=3; --We define minimum value, it can be generated.
DECLARE @MAX INT=6; --We define maximum value, it can be generated.
SELECT @MIN+FLOOR((@MAX-@MIN+1)*RAND(CONVERT(VARBINARY,NEWID()))); --And then this T-SQL snippet generates an integer between minimum and maximum integer values.
You can change and edit this code for your needs.
Here is the simple and single line of code
For this use the SQL Inbuild RAND() function.
Here is the formula to generate random number between two number (RETURN INT Range)
Here a is your First Number (Min) and b is the Second Number (Max) in Range
SELECT FLOOR(RAND()*(b-a)+a)
Note: You can use CAST or CONVERT function as well to get INT range number.
( CAST(RAND()*(25-10)+10 AS INT) )
Example:
SELECT FLOOR(RAND()*(25-10)+10);
Here is the formula to generate random number between two number (RETURN DECIMAL Range)
SELECT RAND()*(b-a)+a;
Example:
SELECT RAND()*(25-10)+10;
More details check this: https://www.techonthenet.com/sql_server/functions/rand.php
Nice and simple, from Pinal Dave's site:
http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 3 ---- The lowest random number
SET @Upper = 7 ---- One more than the highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
(I did make a slight change to the @Upper- to include the upper number, added 1.)
This generates a random number between 0-9
SELECT ABS(CHECKSUM(NEWID()) % 10)
1 through 6
SELECT ABS(CHECKSUM(NEWID()) % 6) + 1
3 through 6
SELECT ABS(CHECKSUM(NEWID()) % 4) + 3
Dynamic (Based on Eilert Hjelmeseths Comment, updated to fix bug( + to -))
SELECT ABS(CHECKSUM(NEWID()) % (@max - @min - 1)) + @min
Updated based on comments:
NEWID
generates random string (for each row in return)CHECKSUM
takes value of string and creates number%
) divides by that number and returns the remainder (meaning max value is one less than the number you use)ABS
changes negative results to positiveYou can do this:
DECLARE @maxval TINYINT, @minval TINYINT
select @maxval=24,@minval=5
SELECT CAST(((@maxval + 1) - @minval) *
RAND(CHECKSUM(NEWID())) + @minval AS TINYINT)
And that was taken directly from this link, I don't really know how to give proper credit for this answer.
Source: Stackoverflow.com