SQL Server 2012 Random string from a list

n1bvdmb6  于 2023-03-07  发布在  SQL Server
关注(0)|答案(7)|浏览(152)

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

zmeyuzjn

zmeyuzjn1#

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

ztyzrc3y

ztyzrc3y2#

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')
xj3cbfub

xj3cbfub3#

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.

mrfwxfqh

mrfwxfqh4#

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.

vhmi4jdf

vhmi4jdf5#

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.

8ehkhllq

8ehkhllq6#

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.

ukxgm1gy

ukxgm1gy7#

select choose((FLOOR(RAND()*3)+1) , 'Bill','Steve','Jack')

相关问题