Generate random names in sql
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?
7 Solutions collect form web for “Generate random names in sql”
select name from table order by newid()
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
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.
Working on this problem currently.
Rand() wont work, you’ll get the same Random number for all 300 rows. NewID() wont give you a value that is usable. You can however use the following formula for a random value between 1 and 5 on every line. NewID and Checksum given you a random number every time, but that includes negative numbers,then you divide by 4 and take the remainder (0-4) so you then take the absolute value of that number and add 1 to it. Then use that random number to select a value from the tables of names.
ABS(Checksum(NewID()) % 4) + 1
Using it in a command:
Create table randomnames (ID int identity, name) insert into randomnames (name) Values ('Tom', 'Dick', 'Harry', 'Jughead', 'Archie' ,'Sally','Sue','Peggy', 'Betty', 'Veronica') update sometablename set Name = case gender when 'M' then (select name from randomnames where ID = ABS(Checksum(NewID()) % 4)) + 1 when 'F' then (select name from randomnames where ID = ABS(Checksum(NewID()) % 4)) + 6 end
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
Or, update many rows like this:
UPDATE myNewTable SET newName=dbo.getRandomName('male') WHERE gender='male'
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();
select top 1 name from RandomNames where gender = 'M' order by newid()