MySQL REPEAT() Function

Introduction

In this chapter, we will learn about the REPEAT() function in MySQL. The REPEAT() function is used to repeat a string a specified number of times. This function is useful for generating repeated patterns, formatting data, or creating placeholders. We will cover the syntax for the REPEAT() function, a complete example of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the REPEAT() function in MySQL is:

SELECT REPEAT(string, count);

• string: The string to be repeated.
• count: The number of times to repeat the string.

Complete Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the REPEAT() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, email) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com'),
('Priya', 'Singh', 'priya.singh@example.com'),
('Amit', 'Kumar', 'amit.kumar@example.com');

2. Using REPEAT() to Repeat a String

We will use the REPEAT() function to repeat the string ‘Hello ‘ three times.

SELECT REPEAT('Hello ', 3) AS repeated_string;

Output:

3. Using REPEAT() with Table Data

Assuming we want to create a repeated pattern using employee first names, we can use the REPEAT() function to repeat the first names three times.

SELECT first_name, REPEAT(first_name, 3) AS repeated_name
FROM employees;

Output:

4. Formatting Data with REPEAT()

We can use the REPEAT() function to create a visual separator for displaying data.

SELECT CONCAT(REPEAT('-', 10), ' ', first_name, ' ', REPEAT('-', 10)) AS formatted_name
FROM employees;

Output:

Important Considerations

• Negative Counts: If the count is less than or equal to 0, the REPEAT() function returns an empty string.
• Performance: Using the REPEAT() function extensively on large datasets can impact performance. Optimize your queries by using it only when necessary and considering the impact on query execution time.
• String Length: Ensure that the repeated string does not exceed the maximum allowed length for strings in MySQL, which is 65,535 bytes.

Conclusion

The REPEAT() function in MySQL is used for generating repeated patterns and formatting data. This chapter covered the syntax for using the REPEAT() function, provided a complete example of its usage, and discussed important considerations.