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)

Rate

Share

Share

Rate