In SQL Server, the T-SQL REPLICATE() function repeats a string value a specified number of times and returns the result.

The function accepts two arguments; the input string, and the number of times it should be repeated.

Syntax

The syntax goes like this:

REPLICATE ( string_expression ,integer_expression )

Where string_expression is the input string. This can be either character or binary data.

And integer_expression is an integer that specifies how many times to repeat the input string. This can any integer type, including bigint.

Note that if the first argument is not of type varchar(max) or nvarchar(max), the function truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, the first argument must be explicitly cast to the appropriate large-value data type.

Example 1 – Basic Usage

Here’s an example of how it works:

SELECT REPLICATE('Dog', 3) AS Result;

Result:

+-----------+
| Result    |
|-----------|
| DogDogDog |
+-----------+

Example 2 – Add a Space

We can also add a space to the previous example:

SELECT REPLICATE('Dog ', 3) AS Result;

Result:

+--------------+
| Result       |
|--------------|
| Dog Dog Dog  |
+--------------+

Although note that this will add a space at the end of the string too.

We could use the TRIM() function to overcome this:

SELECT TRIM(REPLICATE('Dog ', 3)) AS Result;

Result:

+-------------+
| Result      |
|-------------|
| Dog Dog Dog |
+-------------+

Maybe the following example will make this more apparent:

SELECT 
  REPLICATE('Dog ', 3) + '.' AS 'Untrimmed',
  TRIM(REPLICATE('Dog ', 3)) + '.' AS 'Trimmed';

Result:

+---------------+--------------+
| Untrimmed     | Trimmed      |
|---------------+--------------|
| Dog Dog Dog . | Dog Dog Dog. |
+---------------+--------------+

Example 3 – Invalid Replication Count

If the second argument is a negative value, NULL is returned:

SELECT REPLICATE('Dog', -3) AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

Example 4 – A Database Example

Here’s an example of replicating data from a database:

SELECT TOP(3) 
TRIM(REPLICATE(ArtistName + ' ', 3)) AS Result
FROM Artists;

Result:

+----------------------------------------------------+
| Result                                             |
|----------------------------------------------------|
| Iron Maiden Iron Maiden Iron Maiden                |
| AC/DC AC/DC AC/DC                                  |
| Allan Holdsworth Allan Holdsworth Allan Holdsworth |
+----------------------------------------------------+