SQL Server 2012 Random string from a list

Question

say I have 3 values, Bill, Steve, Jack. and I want to randomly update a table with those values, eg

Update contacts set firstname = ('Bill','Steve','Jack') where city = 'NY'

how do I randomize these values?

Thanks

Answer 1

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

Answer 2

Here's some love using choose

with cte as (
   select *, (ABS(CHECKSUM(NewId())) % 3) + 1 as n
   from contacts
   where city = 'NY'
)
update cte
set firstname = choose(n, 'Bill','Steve','Jack')

Answer 3

You can do something like this

-- Storing the list of strings in a CTE

WITH PossibleValues AS 
( SELECT 'Bill' AS Name, 
          1 AS Number
  UNION SELECT 'Steve' AS NAME, 
         2 AS Number
  UNION SELECT 'Jack' AS NAME,
         3 AS Number
 )

UPDATE contacts
SET firstname = (SELECT Name
                          FROM PossibleValues
                          WHERE PossibleValues.Number = FLOOR(RAND()*(4-1)+1))
WHERE City = 'NY'

The FLOOR(RAND()*(4-1)+1) would generate a random number from 1 to 3 everytime you run the query. Therefore, you will be picking a random name every time.

Answer 4

This is addition answer using Ben Thul answer:

DECLARE @index INT
SELECT  @index = CAST(RAND() * 3 + 1 AS INT)
UPDATE contacts SET firstname = CHOOSE(@index,'Bill','Steve','Jack') WHERE city = 'NY'

Using RAND function will random values from 1 to 3. Then, based on resulted int value the CHOOSE function will pick values depending on the order/index of the given strings. In which 'Bill' is the index 1 then so on.

You can test the random int values by using below SQL script:

SELECT CAST(RAND() * 3 + 1 AS INT)

I have a weird issue if use the RAND directly to the query for example below:

SELECT CHOOSE( CAST(RAND() * 3 + 1 AS INT),'Bill','Steve','Jack')

There are instance that value is NULL.

Answer 5

This might answer your question:

How do I generate random number for each row in a TSQL Select?

Use RAND to generate a number that determines which one to use.

Answer 6

Tried all suggestions above, ended up indexing to ID to generate a random index on a CHOOSE function

select lastname, ,choose( ((ID % 3)+1),'Bill','John','Mac') as firstname from contacts

where "3" is the number of items of your list. It's not really random as it is indexed to your row id, but it's random enough so each row is different.