Generating random numbers in a range, sql2k
This script will generated random numbers within a choosen Upper and LowerBound.
I'v created a random number (int's) generator since i couldnt find a script which exactly did what I was searching for. The RAND function seems to do repetive patterns, so i created a new function based on the NEWID function.
After running the two create functions, call the randomize function with:
select master.dbo.fx_generateRandomNumber(newID(), 5, 10)
5 = the lowerbound and 10 the upperbound of the range.
Use always the newid() as first param since this will guarantee uniqueness. I could not use it inside the generate function due too sql scalar function limitations.
henk-nospam-@hatchlab.nl
/*
Used to convert Hexidecimal values to Int
author: henk-nospam-@hatchlab.nl
2004-01-09
*/CREATE FUNCTION dbo.fx_convertVarcharHexToDec
(@varHex varchar(8))
RETURNS int
AS
BEGIN
declare @val_int int
declare @val_hex varchar(10)
set @val_hex = @varHex
--convert hex-varchar to integer.
set @val_int =
((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),1,1),'0123456789ABCDEF')-1)*power(16,7))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),2,1),'0123456789ABCDEF')-1)*power(16,6))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),3,1),'0123456789ABCDEF')-1)*power(16,5))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),4,1),'0123456789ABCDEF')-1)*power(16,4))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),5,1),'0123456789ABCDEF')-1)*power(16,3))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),6,1),'0123456789ABCDEF')-1)*power(16,2))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),7,1),'0123456789ABCDEF')-1)*power(16,1))
+ ((charindex(substring(right('00000000'+substring(@val_hex,3,8),
8),8,1),'0123456789ABCDEF')-1)*power(16,0))
--display.
return @val_int
END
GO
/*
Used to generate random ints in the range of [min, ..,max].
The @guid param should always be called with the newid() as value, this will create better series of random numbers
author: henk-nospam-@hatchlab.nl
2004-01-09
UPDATED: 2004-07-20, Artur Szlejter; Fiexed: When lowerbound higher then 0 was choosen it generated numbers between [0-lowerbound].
*/CREATE FUNCTION dbo.fx_generateRandomNumber(
@guid as uniqueidentifier,
@intMin int = 0,
@intMax int = 10 )
RETURNS int
AS
BEGIN
declare @tmp1 as int
declare @tmp2 as numeric(10,3)
declare @tmp3 as numeric(10,3)
set @tmp1 = dbo.fx_convertVarcharHexToDec('0x' + right(cast
(@guid as varchar(64)), 2))
set @tmp2 = (@intMax - @intMin) / cast(255 as numeric(10,3))
--filter factor
set @tmp3 = (@tmp1 * @tmp2) + @intMin
return cast(round(@tmp3, 0) as int)
END
GO
/*
This will get a random integer between 5 and 10!
Always use the newid() as parameter value for the first parameter!
*/select master.dbo.fx_generateRandomNumber(newID(), 5, 10) 
