MySQL REPEAT() Function

The REPEAT() function in MySQL is used to repeat a string a specified number of times.

Syntax

REPEAT(string, number)

Parameters

Definition and Usage

• Purpose: The REPEAT() function repeats a string a given number of times.
  • If the number is 0 or negative, the function returns an empty string.
  • The function is often used for generating repeated patterns or to format data by repeating certain text.

Usage Examples

Example 1: Basic Usage

Repeat the string "SQL Tutorial" 3 times:

SELECT REPEAT("SQL Tutorial", 3);

Output:

Explanation: The string "SQL Tutorial" is repeated 3 times.

Example 2: Repeating a Column Value

Repeat the text in the CustomerName column 2 times:

SELECT REPEAT(CustomerName, 2)
FROM Customers;

Output (example data):

Explanation: The CustomerName value is repeated twice for each customer.

Technical Details

• Works In: From MySQL 4.0 onwards.
• Return Type: The function returns a string that is the result of repeating the original string the specified number of times.
• Number Parameter: If the number is 0 or negative, the function returns an empty string.
• Performance Considerations: Repeating large strings multiple times could have performance implications if used extensively in queries, particularly when handling large datasets.

Applications

1. Formatting Data: You can use REPEAT() to format output by repeating text a specified number of times (e.g., repeating dashes or asterisks for separators).
2. Generating Patterns: It can be useful for generating repeated patterns in reports or in the construction of certain data formats.
3. Testing and Prototyping: You might use REPEAT() in testing scenarios, especially when you need repeated strings for mock data or during development.

Example Use Case

Imagine you are building a report and want to display a repeated separator line for every entry:

SELECT REPEAT("-", 20) AS Separator;

Output:

This will produce a separator line made up of 20 hyphens, useful in visual outputs or reports.

Key Notes

• The REPEAT() function can be useful for various string manipulation tasks, including formatting and data generation.
• The number parameter should be positive; otherwise, it returns an empty string.

The REPEAT() function is a simple but versatile tool for string manipulation in MySQL, enabling you to repeat strings as needed for various practical applications.