MySQL REPEAT() Function – [ Easily Explained ]

By /
REPEAT Function

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

REPEAT(string, number_of_times);Code language: SQL (Structured Query Language) (sql)

Where

  • ‘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);Code language: SQL (Structured Query Language) (sql)

The above query says that we want the string “**#” to repeat six times. We get the output as follows.

MySQL Repeat Basic Example

Repeating Numbers

MySQL 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);Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Repeat Numbers

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);Code language: SQL (Structured Query Language) (sql)

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 Numbers Format

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);Code language: SQL (Structured Query Language) (sql)

And we get the output as,

MySQL Repeat Negative Value

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);Code language: SQL (Structured Query Language) (sql)

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,

Repeat Null Examples

Using MySQL REPEAT() With Tables

Consider the below ‘Employee’ table.

Format Employee Table
Employee Table

Simple Example

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;Code language: SQL (Structured Query Language) (sql)

And the output is,

Repeat Table Example1

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.

Let us write a query that displays the UID and Name of the employee. We will use CONCAT(), LEFT() and RIGHT() functions.

SELECT CONCAT(Office_Code, REPEAT('0', 3), RIGHT(Date_Joined, 2), LEFT(Name, 1)) AS UID, Name FROM Employee;Code language: SQL (Structured Query Language) (sql)

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 CONCAT() function.

The output is,

Repeat Table Example2

Conclusion

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.

References