Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

PRNG In SQL Select

DZone's Guide to

PRNG In SQL Select

·
Free Resource
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
Topics:

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}