CODEWITHSUNDEEP
sqlsql-server
GeorgeB
GeorgeB

Reputation: 848

How do I insert random characters into a sql database column?

I want to populate a column of my database table with 253 lines of 'M' and 'F' randomly placed in the column, is this possible?

Example of what it may look like:

Gender:
M
M
F
M
F
F
M
F
M

Upvotes: 6

Views: 1849

Answers (3)

Pavel Morshenyuk
Pavel Morshenyuk

Reputation: 11471

For MS SQL you can use NEWID and CHECKSUM functions like:

  UPDATE Users 
  SET Gender = (CASE WHEN ABS(CHECKSUM(NEWID()) % 2) = 1 THEN 'M' ELSE 'F' END)

  • NEWID() will generate random GUID

  • CHECKSUM() will generate hash of that GUID

  • ABS() to make it either 1 or 0

WARNING! While some people suggesting to use RAND function - please do not use it for this particular case. The query like this: 

UPDATE Users SET Gender = CASE WHEN (RAND() > 0.5) THEN 'M' ELSE 'F' END

.. will result that you have all values either M or either F.

Potentially you can seed RAND function with some value like Id, but distribution of values will be not very good: like first 30-40% all M, then 30-40% all F, then M again.

Upvotes: 5

Ali Elmi
Ali Elmi

Reputation: 386

You can use RAND()function.

Please read the following URL: [enter link description here][1]

https://learn.microsoft.com/en-us/sql/t-sql/functions/rand-transact-sql

Upvotes: 0

Chester Lim
Chester Lim

Reputation: 519

Try this so you can decrease "0.5" to improve the chances of being picked if you want the "M" to be more dominant. 

 SELECT CASE WHEN (RAND() > 0.5) THEN 'M' ELSE 'F' END

Upvotes: 0

Related Questions