This article demonstrates how to repeat a string multiple times in MySQL using the REPEAT() function.

As the name suggests, the REPEAT() function can be used to repeat a string. More specifically, it allows you to specify how many times the string should be repeated.

Syntax

Here’s how the syntax goes:

REPEAT(str,count)

Where str is the string to repeat, and count is the number of times you want it repeated.

Example

Here’s an example to demonstrate:

SELECT REPEAT('Cat', 3);

Result:

+------------------+
| REPEAT('Cat', 3) |
+------------------+
| CatCatCat        |
+------------------+

Of course, you can add a space if required:

SELECT REPEAT('Cat ', 3);

Result:

+-------------------+
| REPEAT('Cat ', 3) |
+-------------------+
| Cat Cat Cat       |
+-------------------+

Repeating Characters when Concatenating Strings

You can use REPEAT() as an argument to another function. For example, you could use it to repeat a delimiter or other character when concatenating strings:

SELECT CONCAT('Cat', REPEAT('.', 7), 'Meow!') AS Result;

Result:

+-----------------+
| Result          |
+-----------------+
| Cat.......Meow! |
+-----------------+

Repeats Less than 1

If you specify the number of repeats to be less than 1, you’ll end up with the empty string:

SELECT REPEAT('Cat', 0);

Result:

+------------------+
| REPEAT('Cat', 0) |
+------------------+
|                  |
+------------------+

NULL Repeats

You’ll also get a NULL if you provide NULL as the number of repeats:

SELECT REPEAT('Cat', NULL);

Result:

+---------------------+
| REPEAT('Cat', NULL) |
+---------------------+
| NULL                |
+---------------------+

Repeating Blank Spaces

While you can certainly  use REPEAT() to repeat the space character, if the space character is all you need repeated, consider using the SPACE() function, which provides a more concise way to repeat the space character.