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 |