Home » REPLICATE Function in SQL

REPLICATE Function in SQL

by Online Tutorials Library

REPLICATE Function in SQL

The REPLICATE is a string function in SQL. It shows the inputted string in the output to the given number of times.

Syntax of REPLICATE String Function

Syntax1: This syntax describes how to use the REPLICATE with the fields of the structured table.

If we want to perform Replicate function, then we have to specify the name of that column from the table whose values we want to repeat.

Syntax2: This syntax descibes how to use the REPLICATE function with the string or sentence:

Syntax3: This syntax descibes how to use the REPLICATE function with the individual character:

Examples of REPLICATE String function

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

Output:

REPLICATE_4_string
tutoraspire tutoraspire tutoraspire tutoraspire

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

Output:

REPLICATE_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:

REPLICATE_5_character
SSSSS

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

REPLICATE_6_numbers
9825 9825 9825 9825 9825 9825

Example 5: In this example, we are going to perform the REPLICATE function on the string and integer columns of the table.

So, first we will create the new table through which we will perform the REPLICATE function.

The below CREATE query shows how to create the new table in the Structured Query Language:

Now, we create the new table Student_Sem_Marks using the above CREATE syntax which helps to store the marks of each semester of college students.

The following multiple INSERT queries inserts the records of students with names and their semester marks:

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

Student_ID Student_First_Name Student_Last_Name First_Sem Second_Sem Third_Sem Fourth_Sem Total Percentage Grade
11254 Akash Sharma 58.857 65.547 98.245 60.254 282.903 70 B1
11250 Ram Sharan 28.247 58.987 78.548 77.254 243.036 60 C2
11255 Manoj Gupta 55.847 25.548 68.244 88.785 238.424 78 B1
11257 Parul Chaudhary 85.547 98.278 62.525 35.858 281.935 65 B2
11251 Monu Sharma 72.254 63.354 36.258 96.639 268.505 67 B2
11258 Srishti Chaudhary 85.589 78.478 98.785 89.698 352.55 88 A2
11260 Bhavesh Bardiya 78.256 87.658 82.963 91.365 340.242 85 A2

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

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

Output:

Student_First_Name REPLICATE_3FirstName
Akash AkashAkashAkash
Ram RamRamRam
Manoj ManojManojManoj
Parul ParulParulParul
Monu MonuMonuMonu
Srishti SrishtiSrishtiSrishti
Bhavesh BhaveshBhaveshBhavesh

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

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

Output:

Student_Last_Name REPLICATE_2LastName
Sharma SharmaSharma
Sharan SharanSharan
Gupta GuptaGupta
Chaudhary ChaudharyChaudhary
Sharma SharmaSharma
Chaudhary ChaudharyChaudhary
Bardiya BardiyaBardiya

Query 3: The following SELECT query uses the REPLICATE function with the Grade column of the above Student_Sem_Marks table:

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

Output:

Grade REPLICATE_4Grade
B1 B1B1B1B1
C2 C2C2C2C2
B1 B1B1B1B1
B2 B2B2B2B2
B2 B2B2B2B2
A2 A2A2A2A2
A2 A2A2A2A2

Query 4: The following SELECT query uses the REPLICATE function with the concatenation of first name and last name column of the above Student_Sem_Marks table:

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

Output:

Student_First_Name Student_Last_Name REPLICATE_2Studentname
Akash Sharma AkashSharmaAkashSharma
Ram Sharan RamSharanRamSharan
Manoj Gupta ManojGuptaManojGupta
Parul Chaudhary ParulChaudharyParulChaudhary
Monu Sharma MonuSharmaMonuSharma
Srishti Chaudhary SrishtiChaudharySrishtiChaudhary
Bhavesh Bardiya BhaveshBardiyaBhaveshBardiya

You may also like