SQL REPEAT

How does the SQL REPEAT function work?

Returns a string value repeated a specified number of times.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL REPEAT: MySQL, MariaDB, PostgreSQL, SQLite, Snowflake. SQL Server uses REPLICATE. Oracle has no direct equivalent.

SQL REPEAT Full Explanation

REPEAT is a string-building function that concatenates a given character expression to itself a defined number of times. Because it runs in the database engine it performs faster than application-side loops and guarantees identical collation and encoding as the source data. If the repeat count is zero or negative, an empty string is returned. When the count is NULL the entire result is NULL. Some dialects silently truncate the result if it exceeds the maximum string length for that data type. REPEAT is non-standard SQL but is widely implemented with identical or very similar behavior.

SQL REPEAT Syntax

REPEAT(string_expression, repeat_count);

SQL REPEAT Parameters

string_expression (STRING) - The text to duplicate.
repeat_count (INT) - How many times to append string_expression to itself.

Example Queries Using SQL REPEAT

-- Basic repetition
SELECT REPEAT('abc', 3) AS repeated;

-- Duplicate a column value twice (MySQL)
SELECT id, REPEAT(username, 2) AS doubled_name
FROM users
LIMIT 5;

-- PostgreSQL variant (function name is identical but case-insensitive)
SELECT repeat('xy', 4);

Expected Output Using SQL REPEAT

The first query returns a single row with the value 'abcabcabc'
The second query shows each username repeated twice
The PostgreSQL example outputs 'xyxyxyxy'

Use Cases with SQL REPEAT

Build test strings or padding quickly inside SQL.
Generate fixed-length keys by repeating patterns.
Create simple data mocks for load testing.
Construct CSV-like separators or visual dividers directly in result sets.

Common Mistakes with SQL REPEAT

Passing a negative repeat_count expecting an error; most dialects just return ''.
Forgetting that NULL repeat_count yields NULL, not ''.
Assuming unlimited length; some engines truncate large outputs.
Using REPEAT in SQL Server (use REPLICATE instead).

First Introduced In

MySQL 3.23; adopted by PostgreSQL 8.3

Frequently Asked Questions

What happens when the count is zero?

The function returns an empty string, not NULL.

Can I use REPEAT in SQL Server?

No. SQL Server offers the REPLICATE function, which is syntactically similar.

Does REPEAT handle multibyte characters?

Yes. The function repeats the sequence of bytes. Ensure your collation and character set support the characters being repeated.

Is there a length limit?

Yes. The output cannot exceed the maximum size of the underlying string type (e.g., 65,535 bytes in MySQL VARCHAR).