Random Name Generation Method

Keywords: SQL Sever SQL Spring Database

 From: http://www.maomao365.com/?p=10025

Abstract:
Following is the method of using sql script to generate Chinese names to share, as follows:
Experimental environment: sql server 2008 R2
In our work, we sometimes need to generate random names in batches. The following will describe how to use sql scripts to generate random "names" to share, as follows:
Implementation ideas:
1. Define a family name Library
2. Define a name base
3. Use rand to select random rows and then combine them into a new name.

DECLARE @maomao_Xing TABLE(keyId INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20)) -- Surname
DECLARE @maomao_Ming TABLE(keyId INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20)) -- Name

INSERT @maomao_Xing VALUES
('Zhao'),('money'),('Grandchildren'),('Plum'),('west'),('Wu'),('Zheng'),('king'),('Feng'),('Chen'),('Paper mulberry'),('Wei'),('a surname'),('Shen'),('Han'),('Yang'),
('Zhu'),('Qin'),('especially'),('Xu'),('What'),('Lu'),('Shi'),('Zhang'),('hole'),('Cao'),('strict'),('Hua'),('gold'),('Wei'),('pottery'),('ginger'),
('Qi'),('thank'),('Zou'),('Metaphor'),('Cypress'),('water'),('sinus'),('chapter'),('cloud'),('Soviet'),('Pan'),('Arrowroot'),('Xi'),('Fan'),('Peng'),('Lang'),
('Lu'),('Wei'),('Chang'),('Horse'),('seedling'),('Phoenix'),('FLOWER'),('square'),('a surname'),('let'),('Yuan'),('Willow'),('A kind of'),('abalone'),('history'),('Tang Dynasty'),
('fee'),('Lian'),('Cen'),('Xue'),('Thunder'),('Congratulate'),('Ni'),('soup'),('Teng'),('Yin'),('Luo'),('BI'),('Hao'),('Wu'),('security'),('often'),
('Happy'),('to'),('Time'),('Fu'),('skin'),('Bian'),('Qi'),('Kang'),('Five'),('more than'),('element'),('Divination'),('Gu'),('Meng'),('flat'),('yellow'),
('and'),('Mu'),('Xiao'),('Yin'),('Lai')

INSERT @maomao_Ming VALUES ('love'),('security'),('hundred'),('state'),('Treasure'),('Protect'),('Embrace'),('shellfish'),('times'),('Bei'),('book'),
('have to'),('Green jade'),('BI'),('Bin'),('ice'),('soldier'),('Bing'),('step'),('color'),('Cao'),('Chang'),('long'),('often'),('exceed'),
('towards'),('Chen'),('morning'),('become'),('present'),('Bear'),('Sincerity'),('Worship'),('Chu'),('pass'),('spring'),('pure'),('Cui'),('village'),
('temple'),('Ding'),('set'),('east'),('winter'),('Two'),('all'),('square'),('Fragrant'),('A kind of'),('fly'),('phenanthrene'),('Numerous'),('Fen'),
('Exciting'),('wind'),('peak'),('front'),('Phoenix'),('Fu'),('Blessing'),('pay'),('complex'),('rich'),('change'),('Just.'),('high'),('Pavilion'),
('chromium'),('root'),('Geng'),('Plowing'),('common'),('Work'),('crown'),('light'),('wide'),('return'),('GUI'),('country'),('sea'),('cold'),
('Han'),('Hao'),('Hao'),('Lotus'),('red'),('macro'),('flood'),('Hung'),('thick'),('Hua'),('Deposit'),('large'),('Dan'),('Avenue'),
('Virtue'),('Deng'),('Whetstone'),('Dian'),('Tenancy'),('Small'),('Dog'),('Dear')

-- Random Generation of 1000 Names
declare @t table(name nvarchar(3))
declare @i int 
set @i=0

while @i <1000
begin
insert into @t (name) 
SELECT RTRIM((SELECT NAME FROM @maomao_Xing WHERE keyId = Round(Rand()*(100-1)+1,0)))
+RTRIM(LTRIM((SELECT NAME FROM @maomao_Ming WHERE keyId = Round(Rand()*(100-1)+1,0))))
+RTRIM(LTRIM((SELECT NAME FROM @maomao_Ming WHERE keyId = Round(Rand()*(100-1)+1,0)))) AS [Random name]
set @i =@i+1 
end

select * from @t as t 

 

Relevant reading:
The RAND mathematical function will return a float type of 0-1
Method Sharing of a Data in Random Query Database Tables

Posted by blui on Sat, 12 Oct 2019 10:49:27 -0700