PRNG In SQL Select

jokeyxero (xero) — Thu, 17 May 2007 00:39:32 GMT

Simple example of a PRNG (pseudo-random number generator) written into a SQL statement

Example is in T-SQL, but it ports well

Actual application should use either a better random algorithm, or the output be used with randomized seeds. This is definitely not cryptographically secure. It's very handy if you need a simple random number with your recordset though.



-- Setup some vars we'll need

DECLARE @prng TABLE (seed BIGINT, rnum nchar(10))

DECLARE @seeds TABLE (seed BIGINT)

DECLARE @seed BIGINT

DECLARE @C1 BIGINT, @C2 BIGINT, @C3 BIGINT

SET @seed = 0

SET @C1 = 1664525

SET @C2 = 4294967296

SET @C3 = 1013904223



-- Create a seed table so we can have some data to use

WHILE @seed < 10

BEGIN

    INSERT INTO @seeds (seed) VALUES (@seed)

    SET @seed = @seed + 1

END



-- Create our PRNG (inserts into table for illustrative purposes)

-- prng(seed) ::= ((((C1 * seed) % C2) + C3) % C2) / C2

-- Then convert prng(seed) into a string

-- of 10 chars, 8 of which are decimal places

INSERT INTO @prng

SELECT

    seed,

    REPLACE(

        STR(

            ( CAST((((@C1*seed)%@C2)+@C3)%@C2 AS FLOAT) )

            / ( CAST(@C2 AS FLOAT)),

            10, 8

        ),

        ' ', '0') AS rnum

FROM @seeds



-- Let's take a look at what we created

SELECT * FROM @prng

DZone Snippets: prng code

PRNG In SQL Select