Generate random names in sql

Question

I have 5 random names each for male and female. I need to insert random names based on the gender. But how can we insert names in random from a set of 5 names in SQL. Is it possible?

I am sure it is possible, but can you post some sample data/desired result and any queries that you have tried to solve your problem. — Taryn, Jul 02 '13 at 17:11
I have a weird question that I hope you'll answer - what are you using this for? I can't think of anything off the top of my head that would require a randomly selected name from a fixed list. Pokemon Trainer creation? — gloomy.penguin, Jul 02 '13 at 17:34
@gloomy.penguin: I often use random names, random addresses, random date ranges, etc., for testing. — Mike Sherrill 'Cat Recall', Jul 02 '13 at 17:59
http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table — JsonStatham, Aug 23 '13 at 08:54

score 10 · Answer 1 · edited Aug 11 '17 at 12:02

10

select name from table order by newid()

edited Aug 11 '17 at 12:02

TheGameiswar

27,855
8
56
94

answered Jul 02 '13 at 17:15

Md. Parvez Alam

4,326
5
48
108

this will always give random list – Md. Parvez Alam Jul 02 '13 at 17:16
1

and if you don't want to make a real table, you can always do `select top 1 name from (select 'john' as name union select 'tim' as name....) as male_names order by newid()` – gloomy.penguin Jul 02 '13 at 17:18
Exactly how would this work.... according to online searches: http://msdn.microsoft.com/en-us/library/ms190348.aspx it creates a unique GUID type ID... this doesnt seem like it would work. – logixologist Jul 02 '13 at 17:19
I tried it and its an interesting concept. But you forgot to select TOP 1. Here is what code I used to test it. It always returned all 3. CREATE TABLE #tmp1234 ( name varchar(10) ) Insert into #tmp1234 (name) values ('Mary') Insert into #tmp1234 (name) values ('Jack') Insert into #tmp1234 (name) values ('Jill') select name from #tmp1234 order by newid() – logixologist Jul 02 '13 at 17:22
exactly, if you want to insert one row at a time, i assumed, as he asked to insert random names, – Md. Parvez Alam Jul 02 '13 at 17:25
The query I posted above does run if you either take out `...` or replace it with more instances of `union select 'name' as name` and it will demonstrate how the idea @Md.ParvezAlam said works. – gloomy.penguin Jul 02 '13 at 17:29
@logixologist try to execute the query 2 or 3 times, it will give you name in random order – Md. Parvez Alam Jul 02 '13 at 17:51

score 5 · Answer 2 · answered Jul 02 '13 at 17:18

Create a table with the names, and an integer ID. Then use RAND() with % 5 to get down to a value between 0 and 4 inclusively. Add 1 if you want a male, and 6 if you want a female. Like so:

Create table RandomNames
(id int,
 name varchar(100),
 gender char(1)
)

insert into RandomNames
(id, name,gender)
select 1,'Bill','M'
union
select 2,'John','M'
union
select 3,'Steve','M'
union
select 4,'Mike','M'
union
select 5,'Phil','M'
union
select 6,'Sarah','F'
union
select 7,'Ann','F'
union
select 8,'Marie','F'
union
select 9,'Liz','F'
union
select 10,'Stephanie','F'

declare @wantedGender char(1)

select @wantedGender = 'M'

select name 
from RandomNames
where id =  (CAST(RAND()*100 as int) % 5) + case when @wantedGender = 'M' then 1 else 6 end

score 1 · Answer 3 · answered Jul 02 '13 at 17:14

1

Store the 5 random names for male in one table and the 5 random names for female in another table. Select a random integer between 1 and 5 and cross reference to male or female table using an inner join.

answered Jul 02 '13 at 17:14

rgrano

351
1
6

Thanks a lot. But how do I do this for about 300 Rows. They want me to use the given 5 names and insert them randomly into 300 rows. – user2471553 Jul 02 '13 at 17:19

score 1 · Answer 4 · answered Aug 23 '13 at 18:46

1

Create a function

CREATE FUNCTION [dbo].[getRandomName](@gen varchar(10))
RETURNS @name varchar(50)

AS
BEGIN

   SELECT TOP 1 @name = name FROM table WHERE gender=@gen ORDER BY newid()
END

Then, just pass @gen to the function like this

select dbo.getRandomName('male')

Or, update many rows like this:

UPDATE myNewTable 
   SET newName=dbo.getRandomName('male')
WHERE gender='male'

answered Aug 23 '13 at 18:46

davids

5,397
12
57
94

"Invalid use of a side-effecting operator 'newid' within a function." – Russell Fox Sep 14 '18 at 18:42

score 1 · Answer 5 · edited Aug 24 '13 at 02:11

In SQL Server, the best way to get "random" is to use newid(). You can sort by this to get a sorted list.

If you have five names for each gender, you can use a CTE to store them. The insert would then look like:

with names as (
      select 'M' as gender, 'Alexander' as name union all
      select 'M', 'Burt' union all
      select 'M', 'Christopher' union all
      select 'M', 'Daniel' union all
      select 'M', 'Eric' union all
      select 'F', 'Alexandra' union all
      select 'F', 'Bertha' union all
      select 'F', 'Christine' union all
      select 'F', 'Daniela' union all
      select 'F', 'Erica'
)
insert into table(name)
    select top 1 name
    from names
    where gender = @MyGender
    order by newid();

score 0 · Answer 6 · answered Aug 24 '13 at 07:38

0

select top 1 name from RandomNames 
where gender = 'M' order by newid()

Sample fiddle

answered Aug 24 '13 at 07:38

Praveen Prasannan

7,093
10
50
70

Generate random names in sql

6 Answers6

Linked

Related