Repeat Strings in MySQL

MySQL provides the REPEAT() function to repeat strings a specified number of times. This function can be useful in data formatting, pattern generation, and other scenarios.

In MySQL, you may sometimes need to repeat a string multiple times to create a specific format, pattern, or generate test data.

Using the REPEAT() Function in MySQL

The REPEAT() function in MySQL takes two arguments: the string you want to repeat and the number of times to repeat it. It returns the original string concatenated the specified number of times. Let’s look at some practical examples to illustrate how this function works in MySQL.

To demonstrate, we’ll set up a sample table with a text column and use the REPEAT() function to create repeated strings based on the data.

Setting Up an Example Table

We’ll create a table called patterns with a pattern column to store our string data for repetition.

1. Create a new database called sample_db (if it doesn’t exist):

</>
Copy
CREATE DATABASE IF NOT EXISTS sample_db;

2. Select the sample_db database:

</>
Copy
USE sample_db;

3. Create the patterns table with a pattern column:

</>
Copy
CREATE TABLE patterns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pattern VARCHAR(100)
);

4. Insert sample data into the patterns table:

</>
Copy
INSERT INTO patterns (pattern)
VALUES ('*'), ('#'), ('AB');
Repeat Strings in MySQL - Setup database for examples

Now that our table is set up, we can use the REPEAT() function to work with the data.

Examples: Repeating Strings in MySQL

Below are some examples of using the REPEAT() function to create repeated patterns in MySQL.

patterns table

1. Creating a Simple Repeated Pattern of Column Data

To repeat the string stored in the pattern column five times:

</>
Copy
SELECT pattern, REPEAT(pattern, 5) AS repeated_pattern
FROM patterns;

This query returns each pattern repeated five times, producing results like “*****” for “*”.

Repeat Strings in MySQL - Example - Creating a Simple Repeated Pattern of Column Data

2. Creating a Separator Line with REPEAT()

To create a visual separator line by repeating the dash character 20 times:

</>
Copy
SELECT REPEAT('-', 20) AS separator_line;

This query generates a line of 20 dashes, which could be used as a text separator in reports or formatted output.

Repeat Strings in MySQL - Example - Creating a Separator Line with REPEAT()

3. Using REPEAT() with CONCAT() for Custom Formatting

To create a formatted output where a pattern is repeated and appended with additional text:

</>
Copy
SELECT CONCAT(REPEAT('*', 10), ' End of Section') AS formatted_output;

This query returns a string with a repeated pattern of asterisks, followed by the text “End of Section”.

Repeat Strings in MySQL - Example - Using REPEAT() with CONCAT() for Custom Formatting

4. Using REPEAT() to Generate Test Data

To create a long string of “X” characters for testing purposes, for example, a 50-character string:

</>
Copy
SELECT REPEAT('X', 50) AS test_string;

This query generates a string with 50 “X” characters, useful for testing field capacity or formatting.

Repeat Strings in MySQL - Example - Using REPEAT() to Generate Test Data