PRNG In SQL Select
Join the DZone community and get the full member experience.Join For Free
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
Opinions expressed by DZone contributors are their own.