REPEAT Function in SQL

The REPEAT string function shows the string in the output to the given number of times.

Syntax of REPEAT String Function

Syntax1: This syntax uses the REPEAT function with the column name of the SQL table:

In the syntax, we have to specify the name of that column on which we want to perform the REPEAT function.

Syntax2: This syntax uses the REPEAT function with the string:

Syntax2: This syntax uses the REPEAT function with the individual character:

Examples of REPEAT String function

Example 1: The following SELECT query shows the JavaTpoint string four times in the result:

Output:

REPEAT_4_string

JAVATPOINT JAVATPOINT JAVATPOINT JAVATPOINT

Example 2: The following SELECT query shows the given set of words two times in the output:

Output:

REPEAT_2_string

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

Example 3: The following SELECT query shows the S character five times in the result:

Output:

REPEAT_5_character

SSSSS

Example 4: The following SELECT query shows the set of numbers six times in the result:

Output:

REPEAT_6_numbers

9825 9825 9825 9825 9825 9825

Example 5: This example uses the REPEAT function with the table in Structured Query Language.

To understand the REPEAT function with SQL, we have to create the SQL table first using CREATE statement. The syntax for creating the new table in the SQL database is as follows:

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)
);  

The following CREATE statement creates the Student_Marks table:

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 
);

The below INSERT queries insert the records of college Faculties in the Student_Marks table:

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);

The following SELECT statement displays the inserted records of the above Student_Marks table:

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

Query 1: The following SELECT query uses the REPEAT function with the Student_First_Name column of the above Student_Marks table:

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

This SQL statement shows the First name of each student three times in the result.

Output:

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

Query 2: The following SELECT query uses the REPEAT function with the Student_Last_Name column of the above Student_Marks table:

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

This SQL statement shows the last name of each student two times in the result.

Output:

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

Query 3: The following SELECT query uses the REPEAT function with the Student_Address column of the above Student_Marks table:

SELECT Student_State, REPEAT(Student_State, 4) AS REPEAT_4State FROM Student_Marks;

This SQL statement shows the state four times of each student in the result.

Output:

Student_State	REPEAT_4State
Punjab	PunjabPunjabPunjabPunjab
Haryana	HaryanaHaryanaHaryanaHaryana
Uttar Pradesh	Uttar PradeshUttar PradeshUttar PradeshUttar Pradesh
Rajasthan	RajasthanRajasthanRajasthanRajasthan
Punjab	PunjabPunjabPunjabPunjab
Uttar Pradesh	Uttar PradeshUttar PradeshUttar PradeshUttar Pradesh
Uttar Pradesh	Uttar PradeshUttar PradeshUttar PradeshUttar Pradesh

Query 4: The following SELECT query uses the REPEAT function with the Student_Middle_Name and Student_City column of the above Student_Marks table:

SELECT Student_Middle_Name, REPEAT(Student_Middle_Name, 2) AS REPEAT_2Middlename, Student_City, REPEAT(Student_City, 3) AS REPEAT_3City FROM Student_Marks;

This SQL statement shows the middle name two times and the city three times for each student in the result.

Output:

Student_middle_Name	REPEAT_2Middlename	Student_City	REPEAT_3City
Roy	RoyRoy	Chandigarh	ChandigarhChandigarhChandigarh
Gurr	GurrGurr	Murthal	MurthalMurthalMurthal
singhania	singhaniasinghania	Ghaziabad	GhaziabadGhaziabadGhaziabad
Chopra	ChopraChopra	Jaipur	JaipurJaipurJaipur
Sharma	SharmaSharma	Chandigarh	ChandigarhChandigarhChandigarh
Singhania	SinghaniaSingahnia	Ghaziabad	GhaziabadGhaziabadGhaziabad
Raheem	RaheemRaheem	Lucknow	LucknowLucknowLucknow