SQL REPEAT: Repeat Strings for Separators, Placeholders and Text Bar Charts

REPEAT(str, n) returns a string with str glued to itself n times in a row. The function sounds trivial, but it is the building block for report separators, placeholders for dynamic queries and compact text charts rendered straight in the SQL output.

Basic syntax

It takes two arguments: what to repeat and how many times. The result is plain text.

SELECT REPEAT('ab', 3);   -- ababab
SELECT REPEAT('-', 20);   -- a 20-dash rule
SELECT REPEAT(' ', 4) || 'indented';

Things worth keeping in mind:

• Any string repeats, not just a single character: REPEAT('=-', 5) gives =-=-=-=-=-.
• The result concatenates cleanly with || and other text.
• The return type is always a string, even with digits inside: REPEAT('0', 3) is '000', not the number 0.

In PostgreSQL and MySQL the function is named and behaves the same way: REPEAT(str, n). ClickHouse offers repeat(s, n) with identical meaning.

Separators and report headers

When a report goes to a console or a text dump, neat rules make it readable. REPEAT saves you from hardcoding long strings of dashes.

SELECT REPEAT('=', 40) AS header_rule
UNION ALL
SELECT '  MONTHLY REVENUE REPORT'
UNION ALL
SELECT REPEAT('=', 40);

You can tie the rule length to the data, for example to the longest name, so the underline matches its width exactly:

SELECT
  name,
  REPEAT('-', LENGTH(name)) AS underline
FROM users
ORDER BY name
LIMIT 5;

Placeholders for dynamic queries

Sometimes you need to assemble a list like ?, ?, ? or $1, $2, $3 for a variable-length array of parameters. REPEAT builds the skeleton, and the trailing separator gets shaved off with TRIM or RTRIM.

-- Build "?,?,?" for an IN-list of 3 values
SELECT RTRIM(REPEAT('?,', 3), ',');   -- ?,?,?

The same trick works for PostgreSQL numbered placeholders when application code generates them and you just want to sanity-check the shape. The rule of thumb: repeat "item plus separator", then trim the final separator, so you never fuss over "do not add a comma after the last one".

Text bar charts and LPAD

The most vivid use case is a tiny histogram right in the result set. Take a numeric metric, divide by a scale, and draw as many blocks as you get.

SELECT
  country,
  COUNT(*)                              AS users_cnt,
  REPEAT('#', (COUNT(*) / 10)::int)     AS bar
FROM users
GROUP BY country
ORDER BY users_cnt DESC;

Each # here stands for 10 users. To line the bars up into a tidy column, add LPAD, which pads a string with spaces on the left up to a fixed width:

SELECT
  dept,
  ROUND(AVG(salary))                                  AS avg_salary,
  LPAD(REPEAT('#', (AVG(salary) / 1000)::int), 30)    AS bar
FROM employees
GROUP BY dept
ORDER BY avg_salary DESC;

REPEAT draws the bar, and LPAD(..., 30) guarantees every row is exactly 30 characters wide, so the chart never drifts. The REPEAT + LPAD/RPAD combo is the workhorse for ASCII visualizations.

Edge cases with n <= 0

This is where the main gotcha hides. If n is zero or negative, REPEAT returns an empty string, not NULL and not an error.

SELECT REPEAT('x', 0);    -- '' (empty string)
SELECT REPEAT('x', -5);   -- '' (empty string)

A few consequences follow:

• In a bar chart, a metric that yields 0 blocks renders an empty string, so the bar is simply invisible. If you want at least a marker, force a minimum: REPEAT('#', GREATEST(value, 1)).
• If n turns out NULL (say, from a NULL in the data), the whole REPEAT result becomes NULL. Guard it with COALESCE(n, 0).
• A fractional n is silently rounded/truncated to an integer, so compute n up front and cast it explicitly with ::int.

Gotcha: it is easy to confuse REPEAT with concatenation. REPEAT('ab', 3) is 'ababab' (one six-character string), not three separate rows. If you actually want table rows, use generate_series, and keep REPEAT for formatting text.