Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL equivalent of =rand()

I have several content tables that I want to fill up with random paragraphs of text. In MS Word, I can simply put =rand() and presto! I get three paragraphs of fresh-off-the-press text.

Is there a SQL script/command that I can use to generate random dictionary words using t-sql?

like image 415
Raj More Avatar asked Jul 14 '09 17:07

Raj More


People also ask

What is Rand in SQL Server?

Is an integer expression ( tinyint, smallint, or int) that gives the seed value. If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same. Repetitive calls of RAND () with the same seed value return the same results.

What is the syntax of the Rand number?

The following illustrates the syntax of the RAND number: The RAND function accepts an optional seed argument with the integer data type. If you call the RAND function with the same seed value within a session, the function will return exactly the same value.

What is rand () function in Python?

The RAND () function returns a random number between 0 (inclusive) and 1 (exclusive). Optional. If seed is specified, it returns a repeatable sequence of random numbers.

How do you generate a random number in SQL?

RAND() Examples in SQL Server. In SQL Server, the T-SQL RAND() function allows you to generate a random number. Specifically, it returns a pseudo-random float value from 0 through 1, exclusive. The function accepts an optional argument which provides the seed value. For any given seed value, the results will always be the same.


1 Answers


;
declare 
    @Lorem nvarchar(max),
    @RowsToGen int,
    @Factor int

select
    @Lorem = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.',
    @RowsToGen = 200

-- strip punctuations
set @Lorem = replace(@Lorem, ',', '')
set @Lorem = replace(@Lorem, '.', '')

;
with
Num1(Pos) as
(
    select cast(1 as int)
    union all 
    select cast(Pos + 1 as int) from Num1 where Pos < len(@Lorem)
),
Words as
(
    select substring(@Lorem, Pos, charindex(' ', @Lorem + ' ', Pos) - Pos) as Word
        from Num1 where Pos <= len(@Lorem) and substring(',' + @Lorem, Pos, 1) = ' '
),
WordsCnt(Factor) as
(
    select @RowsToGen / count(*) + 1 from Words
),
Num2(Pos) as
(
    select cast(1 as int)
    union all 
    select cast(Pos + 1 as int) from Num2 cross join WordsCnt where Pos < WordsCnt. Factor
)
select top (@RowsToGen) 
    Word 
from 
    Num2 
cross join 
    Words
order by newid()
option (maxrecursion 0)     
like image 115
Irawan Soetomo Avatar answered Oct 18 '22 15:10

Irawan Soetomo