SQL REPEAT 函数

一、SQL REPEAT 函数 语法

REPEAT 字符串函数将输出中的字符串显示给定次数。

语法1：此语法使用带有 SQL 表的列名的 REPEAT 函数：

SELECT REPEAT(Column_Name, Repetation_value) AS Alias_Name FROM Table_Name;  

在语法中，我们必须指定要对其执行 REPEAT 函数的列的名称。

语法2：此语法使用带有字符串的 REPEAT 函数：

SELECT REPEAT(Original_String, Repetation_value) AS Alias_Name;  

语法3：此语法使用带有单个字符的 REPEAT 函数：

SELECT REPEAT(Character, Repetation_value) AS Alias_Name;  

二、SQL REPEAT 函数 示例

示例 1：以下 SELECT 查询在结果中显示了四次 JavaTpoint 字符串：

SELECT REPEAT(  'YIIDIAN', 4)AS REPEAT_4_string;  

输出结果为：

REPEAT_4_string

YIIDIAN YIIDIAN YIIDIAN YIIDIAN

示例 2：以下 SELECT 查询在输出中两次显示给定的单词集：

SELECT REPEAT(  'NEW DELHI IS THE CAPITAL OF INDIA', 2) AS REPEAT_2_string;  

输出结果为：

REPEAT_2_string

NEW DELHI IS THE CAPITAL OF INDIANEW DELHI IS THE CAPITAL OF INDIA

示例 3：以下 SELECT 查询在结果中显示 S 字符五次：

SELECT REPEAT(  'S', 5) AS REPEAT_5_character;

输出结果为：

REPEAT_5_character

SSSSS

示例 4：以下 SELECT 查询在结果中显示了六次数字集：

SELECT REPEAT(  '9825 ', 6) AS REPEAT_6_numbers;  

输出结果为：

REPEAT_6_numbers

9825 9825 9825 9825 9825 9825

示例 5：此示例对结构化查询语言中的表使用 REPEAT 函数。

要理解 SQL 的 REPEAT 函数，我们必须首先使用 CREATE 语句创建 SQL 表。在 SQL 数据库中创建新表的语法如下：

CREATE TABLE table_name  
(  
First_Column_of_table Data Type (character_size of 1st Column),    
Second_Column_of_table Data Type (character_size of the 2nd column ),    
Third_Column_of_table Data Type (character_size of the 3rd column),    
...    
  
Last_Column_of_table Data Type (character_size of the Nth column)  
);    

以下 CREATE 语句创建Student_Marks表：

CREATE TABLE Student_Marks  
(  
Student_ID INT NOT NULL PRIMARY KEY,    
Student_First_Name VARCHAR (100),    
Student_Middle_Name VARCHAR (100),    
Student_Last_Name VARCHAR (100),   
Student_Class INT NOT NULL,  
Student_City Varchar(120),  
Student_State Varchar (80),  
Student_Marks INT   
);  

下面的 INSERT 语句在Student_Marks表中插入大学学院的记录：

INSERT INTO Student_Marks (Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) VALUES (4001, Aman, Roy, Sharma, 4, Chandigarh, Punjab, 88);  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES ( 4002, Vishal, Gurr, Sharma, 8, Murthal, Haryana, 95 );  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES (4007, Raj, singhania, Gupta, 6, Ghaziabad, Uttar Pradesh, 91);  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)    
VALUES (4004, Yash, Chopra, Singhania, 9, Jaipur, Rajasthan, 85);  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES (4011, Vinay, Sharma, Roy, 8, Chandigarh, Punjab, 94);  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES (4006, Manoj, singhania, Gupta, 5, Ghaziabad, Uttar Pradesh, 83);  
  
  
INSERT INTO Student_Marks   
(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   
VALUES (4010, Ram, Raheem, Gupta, 9, Lucknow, Uttar Pradesh, 89);  

以下 SELECT 语句显示上述Student_Marks表的插入记录：

SELECT * FROM Student_Marks;  

输出结果为：

Schoolboy_Id	Schoolboy_First_Name	Schoolboy_Middle_Name	Schoolboy_Last_Name	Schoolboy_Class	Schoolboy_City	Schoolboy_State	Schoolboy_Marks
4001	Aman	Roy	Sharma	4	Chandigarh	Punjab	88
4002	Vishal	Gurr	Sharma	8	Murthal	Haryana	95
4007	Raj	singhania	Gupta	6	Ghaziabad	Uttar Pradesh	91
4004	Yash	Chopra	Singhania	9	Jaipur	Rajasthan	85
4011	Vinay	Sharma	Roy	8	Chandigarh	Punjab	94
4006	Manoj	Singhania	Gupta	5	Ghaziabad	Uttar Pradesh	83
4010	Ram	Raheem	Gupta	9	Lucknow	Uttar Pradesh	89

查询 1：以下 SELECT 查询将 REPEAT 函数与上述 Student_Marks 表的 Student_First_Name 列一起使用：

SELECT Student_First_Name, REPEAT(Student_First_Name, 3) AS REPEAT_3FirstName FROM Student_Marks;  

此 SQL 语句在结果中显示每个学生的名字三次。

输出结果为：

Student_First_Name	REPEAT_3FirstName
Aman	AmanAmanAman
Vishal	VishalVishalVishal
Raj	RajRajRaj
Yash	YashYashYash
Vinay	VinayVinayVinay
Manoj	ManojManojManoj
Ram	RamRamRam

查询 2：以下 SELECT 查询对上述 Student_Marks 表的 Student_Last_Name 列使用 REPEAT 函数：

SELECT Student_Last_Name, REPEAT(Student_Last_Name, 2) AS REPEAT_2LastName FROM Student_Marks;  

此 SQL 语句在结果中两次显示每个学生的姓氏。

输出结果为：

Student_Last_Name	REPEAT_2LastName
Sharma	SharmaSharma
Sharma	SharmaSharma
Gupta	GuptaGupta
Singhania	SinghaniaSinghania
Roy	RoyRoy
Gupta	GuptaGupta
Gupta	GuptaGupta