In this tutorial, we will study the MySQL
REPEAT() function. Suppose you have a string and for it to be presentable in your output, you want the string to be repeated a specific number of times. This is where the MySQL
REPEAT() function comes into play.
REPEAT() is used to repeat a string or an expression for a given number of times.
It is one of the string formatting functions of MySQL. It takes a string or an expression as an argument along with the number of times they should be repeated. The repeated value is returned in the result-set.
Syntax of MySQL REPEAT()
- ‘string’ is the string which should be repeated and,
- ‘number _of_times’ is the number of times the ‘string’ should be repeated.
Examples of MySQL REPEAT()
Let us take a look at a few basic examples. Consider the below query.
SELECT REPEAT("**#", 6);
The above query says that we want the string “**#” to repeat six times. We get the output as follows.
REPEAT() works for numerical values too. Suppose we have the number – 532 and we want it to repeat 5 times. The query for it is,
SELECT REPEAT(532, 5);
And the output is,
To make this output more presentable and readable, we can also use the
FORMAT() function outside the
REPEAT() function. Let’s see this using the below query.
SELECT FORMAT(REPEAT(532, 5), 0);
The output of the
REPEAT() function is the input to the
FORMAT() function. The
FORMAT() function formats the number and returns the output with zero decimal places. The output is,
MySQL REPEAT() Using Negative Values
What if you specify a negative number in the ‘number _of_times’ argument? Let us demonstrate this using the below query.
SELECT REPEAT('#', -4);
And we get the output as,
Since we specified a negative number in the ‘number _of_times’ argument,
REPEAT() returns a blank string as the output.
REPEAT() Using NULL Values
If either of the parameters in the MySQL
REPEAT() function is NULL, then the output is also NULL. Let us demonstrate this using the below examples.
SELECT REPEAT(NULL, 2); SELECT REPEAT('%@^#', NULL);
In the first query, we have specified the ‘string’ argument as NULL whereas, in the second query, we have specified the ‘number _of_times’ argument as NULL. As I mentioned earlier,
REPEAT() returns NULL if either of the arguments is NULL.
The output is,
Using MySQL REPEAT() With Tables
Consider the below ‘Employee’ table.
Let’s kick things off with a simple example of
REPEAT() with tables. Let us write a query that repeats each value in the Office_Code column five times using the
REPEAT() function. We will use an alias
RepeatedValue in our
SELECT statement. The query for this is,
SELECT REPEAT(Office_Code, 5) AS RepeatedValue FROM Employee;
And the output is,
Combining MySQL REPEAT() with Other Functions
The Unique Identification Number or UID of an employee is the Office_Code of the employee, followed by zero repeated three times, the two rightmost values in the Date_Joined column and the first letter from the left from the values in the Name column.
SELECT CONCAT(Office_Code, REPEAT('0', 3), RIGHT(Date_Joined, 2), LEFT(Name, 1)) AS UID, Name FROM Employee;
We extract the first letter from the left from the Name column using the
LEFT() function. We extract the two rightmost values in the Date_Joined column for every employee using the
RIGHT() function. We repeat 0 three times using the
REPEAT() function. Finally, we concatenate all the above with the Office_Code values of the corresponding employee using the
The output is,
REPEAT() finds use in string formatting – to present your output in a specified format in MySQL. I would encourage you to practice this function by playing around with it in different use cases.
- MySQL Official Documentation on