MySQL REPEAT() Function

Summary: in this tutorial, you will learn how to use the MySQL REPEAT() function to repeat a string a specified number of times.

Introduction to MySQL REPEAT() function

In MySQL, the REPEAT() function allows you to repeat a string a specified number of times.

Here’s the syntax of the REPEAT() function:

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

In this syntax:

• string. The string that you want to repeat.
• count. The number of times that you want to repeat the string in the resulting string.

The REPEAT() function returns the string that repeats count times. If the count is less than 1, the function returns an empty string.

Also, if str or count is NULL, the REPEAT() function returns NULL.

The REPEAT() function can be very useful when you want to format the data for display.

MySQL REPEAT() function examples

Let’s take some examples of using the REPEAT() function.

1) Simple REPEAT() function example

The following example uses the REPEAT() function to repeat the letter “w” there times:

SELECT REPEAT('w',3);Code language: SQL (Structured Query Language) (sql)

Output:

+---------------+
| REPEAT('w',3) |
+---------------+
| www           |
+---------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the REPEAT() function returns a string "www" that repeats the letter "w" three times.

2) Using the REPEAT() function to draw a bar chart

We’ll use the products table from the sample database for the demonstration:

The following example uses the REPEAT() function to draw a bar chart that displays the stock of the products:

SELECT
    p.productName,
	REPEAT('*',
		CAST(p.quantityInStock * 100 / maxQty as SIGNED) / 10
	) qtyInStock
FROM products p
JOIN (
    SELECT MAX(quantityInStock) AS maxQty
    FROM products
) AS stocks;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------+------------+
| productName                                 | qtyInStock |
+---------------------------------------------+------------+
| 1969 Harley Davidson Ultimate Chopper       | ********   |
| 1952 Alpine Renault 1300                    | *******    |
| 1996 Moto Guzzi 1100i                       | *******    |
| 2003 Harley-Davidson Eagle Drag Bike        | ******     |
| 1972 Alfa Romeo GTA                         | ***        |
| 1962 LanciaA Delta 16V                      | *******    |
...Code language: SQL (Structured Query Language) (sql)

How it works.

First, get the max quantity in stock of the product table using the MAX() function:

SELECT
  MAX(quantityInStock) AS maxQty
FROM
  products;Code language: SQL (Structured Query Language) (sql)

The result of the query has the table alias stocks.

Second, join the products table with the stocks table, calculate the ratio of the stock quantity with the maximum quantity, cast the result as a signed integer, and divide the result by 10 for repeating the asterisks (*):

REPEAT(
  '*',
  CAST(
    p.quantityInStock * 100 / maxQty as SIGNED
  ) / 10
) qtyInStockCode language: SQL (Structured Query Language) (sql)

Summary

• Use the REPEAT() function to repeat a string a specified number of times.