Thought I'd share ... use it as you see fit.
I wrote this for something else I need to poc, etc.
Step 1:
CREATE VIEW dbo.vRand
AS
SELECT RAND() as [RANDOM]
Step 2:
USE SomeDB
GO
/*
=========================================================================================================================
Author: Goran Biljetina
Create date: 2012-11-13
Description: Random number generator between 1 and base^power
LIMITS: Returns positive integers, base and exponent inputs are integers as well (that can be modified as needed)
=========================================================================================================================
*/
CREATE FUNCTION dbo.fnRandNumber
(
@base bigint = 10 --> base number, 10 by default
,@exp int --> exponent value, e.g. max 18 when base = 10
)
RETURNS bigint
AS
BEGIN
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
declare @r int, @ef int, @i int
set @r= @exp+1 --> range or maximum sequence number, how many multiplication loops?
set @ef= 0 --> initial multiplication factor
set @i= 1 --> increment of multiplication factor and decrement of range or sequence
declare @Power10 table (Number bigint, Sequence int);
--> generate sequence buckets based on base number and power value input
begin
while @r >= 0
begin
insert into @Power10 (Number,Sequence)
select power(@base,@ef),(@ef)
set @r = (@r - @i)
set @ef = (@ef + @i)
if @r <= 0
BREAK
else
CONTINUE
end
end
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--> GENERATE RANDOM NUMBER FROM 1 to base^power (max exp=18 when base=10, 8 byte int limit)
declare @rspan bigint, @rnumber bigint, @rseqmax int, @rseq int, @rseed bigint
set @rseqmax = @exp --> get sequence or power limit
set @rseq = (FLOOR(((select * from vRand)*@rseqmax))+1) --> get random sequence w/in limit
set @rseed = (select number from @Power10 where Sequence = @rseq) --> get number seed w/in sequence
set @rspan = (FLOOR((((select * from vRand))*((select * from vRand)*@rseed)))+1) --> get rand span w/in rand number seed
set @rnumber = FLOOR((select * from vRand)*@rspan)+1 --> get rand number w/in span
RETURN(@rnumber);
END
GO
Step 3.
--> SAMPLE SETS:
select dbo.fnRandNumber(10,3)[(10,3)];
select dbo.fnRandNumber(10,5)[(10,5)];
select dbo.fnRandNumber(10,9)[(10,9)];
--> max for base 10 (if return 8 byte signed integer)
select dbo.fnRandNumber(10,18)[(10,18)];
select dbo.fnRandNumber(2,8)[(2,8)];
select dbo.fnRandNumber(2,16)[(2,16)];
select dbo.fnRandNumber(2,32)[(2,32)];
--> max for base 2 (if return 8 byte signed integer)
select dbo.fnRandNumber(2,62)[(2,62)];
And this, basically the same thing in JS:
// get random number within provided base + exponent
// by Goran Biljetina
// code as is :)
function isEmpty(value){
return (typeof value === "undefined" || value === null);
}
var numSeq = new Array();
function add(num,seq){
var toAdd = new Object();
toAdd.num = num;
toAdd.seq = seq;
numSeq[numSeq.length] = toAdd;
}
function fillNumSeq (num,seq){
var n;
for(i=0;i<=seq;i++){
n = Math.pow(num,i);
add(n,i);
}
}
function getRandNum(base,exp){
if (isEmpty(base)){
console.log("Specify value for base parameter");
}
if (isEmpty(exp)){
console.log("Specify value for exponent parameter");
}
fillNumSeq(base,exp);
var emax;
var eseq;
var nseed;
var nspan;
emax = (numSeq.length);
eseq = Math.floor(Math.random()*emax)+1;
nseed = numSeq[eseq].num;
nspan = Math.floor((Math.random())*(Math.random()*nseed))+1;
return Math.floor(Math.random()*nspan)+1;
}
console.log(getRandNum(10,20),numSeq);
//getRandNum(-10,20);
//console.log(getRandNum(-10,20),numSeq);
//console.log(numSeq);