SQL - REPEAT() / REPLICATE()
• REPEAT() (MySQL/PostgreSQL) and REPLICATE() (SQL Server/Azure SQL) generate strings by repeating a base string a specified number of times, useful for formatting, padding, and generating test data
Key Insights
• REPEAT() (MySQL/PostgreSQL) and REPLICATE() (SQL Server/Azure SQL) generate strings by repeating a base string a specified number of times, useful for formatting, padding, and generating test data • These functions handle NULL inputs by returning NULL, require non-negative repeat counts, and have database-specific length limitations that can cause silent truncation • Performance degrades with large repeat counts; for production workloads involving thousands of repetitions, consider application-layer string building or temporary tables
Function Syntax and Basic Usage
The REPEAT() and REPLICATE() functions share identical logic but differ by database vendor. MySQL and PostgreSQL use REPEAT(), while SQL Server and Azure SQL Database use REPLICATE().
-- MySQL/PostgreSQL
SELECT REPEAT('AB', 3) AS result;
-- Returns: 'ABABAB'
-- SQL Server/Azure SQL
SELECT REPLICATE('AB', 3) AS result;
-- Returns: 'ABABAB'
Both functions accept two parameters: the string to repeat and the number of repetitions. The repeat count must be a non-negative integer.
-- PostgreSQL example with zero repetitions
SELECT REPEAT('test', 0) AS empty_string;
-- Returns: ''
-- SQL Server with single repetition
SELECT REPLICATE('X', 1) AS single_char;
-- Returns: 'X'
NULL Handling and Edge Cases
Both functions return NULL when either parameter is NULL. This behavior follows SQL’s standard NULL propagation rules.
-- MySQL
SELECT
REPEAT(NULL, 5) AS null_string,
REPEAT('A', NULL) AS null_count,
REPEAT(NULL, NULL) AS both_null;
-- All return NULL
-- SQL Server
SELECT
REPLICATE(NULL, 5) AS null_string,
REPLICATE('A', NULL) AS null_count,
REPLICATE(NULL, NULL) AS both_null;
-- All return NULL
Negative repeat counts behave differently across databases:
-- MySQL returns NULL for negative counts
SELECT REPEAT('X', -1) AS negative_mysql;
-- Returns: NULL
-- SQL Server returns empty string for negative counts
SELECT REPLICATE('X', -1) AS negative_sqlserver;
-- Returns: ''
Generating Fixed-Width Padding
A common use case is padding strings to fixed widths for report formatting or legacy system integration.
-- PostgreSQL: Right-pad product codes to 10 characters
SELECT
product_code,
product_code || REPEAT(' ', 10 - LENGTH(product_code)) AS padded_code
FROM products
WHERE LENGTH(product_code) < 10;
-- SQL Server: Left-pad invoice numbers with zeros
SELECT
invoice_id,
REPLICATE('0', 8 - LEN(CAST(invoice_id AS VARCHAR))) + CAST(invoice_id AS VARCHAR) AS formatted_invoice
FROM invoices
WHERE LEN(CAST(invoice_id AS VARCHAR)) < 8;
For consistent padding regardless of current length:
-- MySQL: Ensure exactly 15 characters with right padding
SELECT
customer_name,
CONCAT(
LEFT(customer_name, 15),
REPEAT(' ', GREATEST(0, 15 - LENGTH(customer_name)))
) AS fixed_width_name
FROM customers;
Creating Visual Separators and Formatting
Generate visual elements for text-based reports or console output:
-- PostgreSQL: Create table separators
SELECT
REPEAT('-', 50) AS separator
UNION ALL
SELECT 'Product Sales Report'
UNION ALL
SELECT REPEAT('-', 50);
-- SQL Server: Build simple bar charts
SELECT
product_name,
total_sales,
REPLICATE('█', total_sales / 1000) AS sales_bar
FROM (
SELECT
product_name,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY product_name
) AS aggregated
ORDER BY total_sales DESC;
Creating hierarchical indentation for tree structures:
-- MySQL: Display organizational hierarchy
WITH RECURSIVE org_tree AS (
SELECT employee_id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, ot.level + 1
FROM employees e
INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT
CONCAT(REPEAT(' ', level), name) AS hierarchical_name,
level
FROM org_tree
ORDER BY employee_id;
Generating Test Data
Create repeating patterns for testing, data masking, or placeholder content:
-- SQL Server: Generate test email addresses
SELECT TOP 100
CONCAT('user', ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '@test.com') AS email,
REPLICATE('Lorem ipsum dolor sit amet. ', 5) AS bio_placeholder
FROM sys.objects;
-- PostgreSQL: Create dummy credit card masks
SELECT
customer_id,
REPEAT('*', 12) || RIGHT(credit_card_number, 4) AS masked_card
FROM customer_payments;
Building large text blocks for performance testing:
-- MySQL: Generate 1MB text blocks
SELECT
id,
REPEAT('A', 1048576) AS large_text_field
FROM generate_series(1, 10) AS id;
-- SQL Server: Create variable-length test data
SELECT
test_id,
REPLICATE('Sample text block. ', test_id * 10) AS variable_content
FROM (VALUES (1), (2), (3), (5), (10)) AS tests(test_id);
Length Limitations and Truncation
Database systems impose maximum string lengths that affect REPEAT() and REPLICATE() output:
-- SQL Server: REPLICATE() truncates at 8000 bytes for VARCHAR
SELECT LEN(REPLICATE('X', 10000)) AS actual_length;
-- Returns: 8000 (not 10000)
-- Use VARCHAR(MAX) for longer strings
SELECT LEN(CAST(REPLICATE('X', 10000) AS VARCHAR(MAX))) AS actual_length;
-- Returns: 10000
PostgreSQL and MySQL handle large strings more gracefully but still have limits:
-- PostgreSQL: Maximum 1GB per field (practical limit)
SELECT LENGTH(REPEAT('A', 1000000)) AS length_check;
-- Returns: 1000000
-- MySQL: max_allowed_packet setting affects result size
SELECT LENGTH(REPEAT('B', 100000000)) AS length_check;
-- May return NULL or error depending on configuration
Performance Considerations
Repeating strings thousands of times impacts query performance. Benchmark different approaches:
-- SQL Server: Compare performance for large repetitions
SET STATISTICS TIME ON;
-- Approach 1: Direct REPLICATE
SELECT REPLICATE('X', 100000);
-- Approach 2: Recursive CTE for very large strings
WITH numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 100000
)
SELECT STRING_AGG('X', '') WITHIN GROUP (ORDER BY n)
FROM numbers
OPTION (MAXRECURSION 0);
SET STATISTICS TIME OFF;
For bulk operations, set-based approaches outperform row-by-row repetition:
-- PostgreSQL: Efficient bulk padding
UPDATE products
SET product_code = product_code || REPEAT(' ', 10 - LENGTH(product_code))
WHERE LENGTH(product_code) < 10;
-- More efficient than cursor-based updates
Combining with Other String Functions
REPEAT() and REPLICATE() integrate well with other string manipulation functions:
-- MySQL: Center-align text within fixed width
SELECT
title,
CONCAT(
REPEAT(' ', FLOOR((50 - LENGTH(title)) / 2)),
title,
REPEAT(' ', CEIL((50 - LENGTH(title)) / 2))
) AS centered_title
FROM articles;
-- SQL Server: Create masked SSN with custom pattern
SELECT
employee_id,
REPLICATE('*', 3) + '-' + REPLICATE('*', 2) + '-' + RIGHT(ssn, 4) AS masked_ssn
FROM employees;
-- PostgreSQL: Build CSV-like structure
SELECT
REPEAT('column_', generate_series) || REPEAT(',', 1) AS csv_headers
FROM generate_series(1, 10);
These functions provide essential string manipulation capabilities for formatting, testing, and data generation tasks. Choose REPEAT() for MySQL/PostgreSQL environments and REPLICATE() for SQL Server, understanding each database’s specific limitations and performance characteristics.