SQL Server 2012 Random string from a list
You can do this with the following trick:
update c set name=ca.name
from contacts c
outer apply(select top 1 name
from (values('bill'),('steve'),('jack')) n(name)
where c.id = c.id order by newid())ca;
c.id = c.id
is just a dummy predicate that forces sql engine to call subquery for each outer row. Here is the fiddle http://sqlfiddle.com/#!6/8ecca/22
Generating random strings with T-SQL
When generating random data, specially for test, it is very useful to make the data random, but reproducible. The secret is to use explicit seeds for the random function, so that when the test is run again with the same seed, it produces again exactly the same strings. Here is a simplified example of a function that generates object names in a reproducible manner:
alter procedure usp_generateIdentifier
@minLen int = 1
, @maxLen int = 256
, @seed int output
, @string varchar(8000) output
as
begin
set nocount on;
declare @length int;
declare @alpha varchar(8000)
, @digit varchar(8000)
, @specials varchar(8000)
, @first varchar(8000)
declare @step bigint = rand(@seed) * 2147483647;
select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
, @digit = '1234567890'
, @specials = '_@# '
select @first = @alpha + '_@';
set @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @length = @minLen + rand(@seed) * (@maxLen-@minLen)
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
declare @dice int;
select @dice = rand(@seed) * len(@first),
@seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = substring(@first, @dice, 1);
while 0 < @length
begin
select @dice = rand(@seed) * 100
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
if (@dice < 10) -- 10% special chars
begin
select @dice = rand(@seed) * len(@specials)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@specials, @dice, 1);
end
else if (@dice < 10+10) -- 10% digits
begin
select @dice = rand(@seed) * len(@digit)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@digit, @dice, 1);
end
else -- rest 80% alpha
begin
declare @preseed int = @seed;
select @dice = rand(@seed) * len(@alpha)+1
, @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = @string + substring(@alpha, @dice, 1);
end
select @length = @length - 1;
end
end
go
When running the tests the caller generates a random seed it associates with the test run (saves it in the results table), then passed along the seed, similar to this:
declare @seed int;
declare @string varchar(256);
select @seed = 1234; -- saved start seed
exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;
exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;
exec usp_generateIdentifier
@seed = @seed output
, @string = @string output;
print @string;
Update 2016-02-17: See the comments bellow, the original procedure had an issue in the way it advanced the random seed. I updated the code, and also fixed the mentioned off-by-one issue.
Generating random string in T-SQL
This is far from an optimal solution, but it will work as specified:
select char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
+char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
+char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
+char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
+char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
Example output:
WCZOVRCIRELAJUT
T-SQL SELECT Random varchar Value Without TempTable
You can achieve it by using a variable with CASE
expression in following:
DECLARE @rand INT
SET @rand = ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1
SELECT [Col1],
[Col2],
CASE @rand
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
ELSE 'D'
END AS RandColValue,
[Col3]
FROM [dbo].[MyTable]
Or you could achieve it without variable in following:
SELECT [Col1],
[Col2],
CASE ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
ELSE 'D'
END AS RandColValue,
[Col3]
FROM [dbo].[MyTable]
Select Random Numbers from a list
Basically the same as the 2nd approach Gordon uses except it lacks the use of the lag function and therefor will work on SQL-2008.
WITH Data AS(
SELECT *, RowNum = ROW_NUMBER() OVER (ORDER BY NEWID())
FROM sys.objects AS O
),
r AS(
SELECT TOP 1 *, SkipRow = 0
FROM Data
WHERE Data.RowNum = 1
UNION ALL
SELECT d.*, SkipRow = CASE WHEN d.object_id BETWEEN r.object_id -2 AND r.object_id + 2 THEN 1 ELSE 0 END
FROM r
JOIN Data AS D
ON r.RowNum + 1 = D.RowNum
)
SELECT TOP 2 * FROM R
WHERE R.SkipRow = 0
how to pick randome number from the number list
I've had success with ordering the top 1 result of a select by newid():
select top 1 WhateverId from YourTable order by newid();
How to request a random row in SQL?
See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):
Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
How do I generate a random number for each row in a T-SQL select?
Take a look at SQL Server - Set based random numbers which has a very detailed explanation.
To summarize, the following code generates a random number between 0 and 13 inclusive with a uniform distribution:
ABS(CHECKSUM(NewId())) % 14
To change your range, just change the number at the end of the expression. Be extra careful if you need a range that includes both positive and negative numbers. If you do it wrong, it's possible to double-count the number 0.
A small warning for the math nuts in the room: there is a very slight bias in this code. CHECKSUM()
results in numbers that are uniform across the entire range of the sql Int datatype, or at least as near so as my (the editor) testing can show. However, there will be some bias when CHECKSUM() produces a number at the very top end of that range. Any time you get a number between the maximum possible integer and the last exact multiple of the size of your desired range (14 in this case) before that maximum integer, those results are favored over the remaining portion of your range that cannot be produced from that last multiple of 14.
As an example, imagine the entire range of the Int type is only 19. 19 is the largest possible integer you can hold. When CHECKSUM() results in 14-19, these correspond to results 0-5. Those numbers would be heavily favored over 6-13, because CHECKSUM() is twice as likely to generate them. It's easier to demonstrate this visually. Below is the entire possible set of results for our imaginary integer range:
Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 0 1 2 3 4 5
You can see here that there are more chances to produce some numbers than others: bias. Thankfully, the actual range of the Int type is much larger... so much so that in most cases the bias is nearly undetectable. However, it is something to be aware of if you ever find yourself doing this for serious security code.
How to randomly select rows in SQL?
SELECT TOP 5 Id, Name FROM customerNames
ORDER BY NEWID()
That said, everybody seems to come to this page for the more general answer to your question:
Selecting a random row in SQL
Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
Select a random row with sqlite:
SELECT column FROM table
ORDER BY RANDOM() LIMIT 1
Related Topics
Generate Series of Week Intervals for Given Month
Postgres - Comparing Two Arrays
SQL Query for Courses Enrolment on Moodle
PHP Is Truncating Mssql Blob Data (4096B), Even After Setting Ini Values. am I Missing One
Freetds and Unixodbc Character Converting
Undo Log Error: No More Space Left Over in System Tablespace for Allocating Undo Log Pages
Find a Database with a Particular Table or Find a Table in Every Database of SQL Server
How to Perform a SQL 'Not In' Query Faster
How to Extract Certain Nth Character from a String in Sql
How to Generate SQL from Dbplyr Without a Database Connection
SQL Convert Milliseconds to Days, Hours, Minutes
Write a Postgres Get or Create SQL Query
Oracle Date Datatype, Transformed to 'Yyyy-Mm-Dd Hh24:Mi:Ss Tmz' Through SQL
How to Update All Columns of a Record Without Having to List Every Column
Retrieving SQL Queries from Active-Record Queries in Rails 3