REPLICATE Function of SQL Server

In this SQL Server tutorial, you will learn how to use the REPLICATE function in SQL Server.

You will learn where to use the REPLICATE() function with syntax and its definition. Also, you will use the REPLICATE() function with literal string or table columns and present the data visually using this function.

Table of Contents

REPLICATE Function of SQL Server

The REPLICATE function of SQL Server repeats a given character string a specified number of times. You can use the REPLICATE() function for date formatting, visual representation of data, generating test data, etc.

The syntax for using the REPLICATE function is as follows:

REPLICATE ( string_expression , integer_expression )

Where,

REPLICATE(): The function repeats the given string several times.
string_expression: The string_expression parameter specifies the character string you want to repeat.
integer_expression: The integer_expression parameter sets the number of times you want to repeat the string. This integer value can’t be a negative if you provide the negative value, then it will return a NULL value.

For example, execute the below query to replicate the string ‘USA’ three times.

SELECT REPLICATE('USA', 3) AS RepeatedString;

The output of the above query contains the result USAUSAUSA, which is three times the string ‘USA.’ You can replicate the string many times as per your requirement.

Here, you have seen how to replicate the literal string multiple times using the REPLICATE function of SQL Server.

REPLICATE Function of SQL Server with Table Column

The REPLICATE() function can also be used on a table column to replicate values of that column multiple times, or you can format the column value to present consistent or visually present the data.

For example, a ProductSales table with the ProductName and Sales column is shown below.

REPLICATE Function of SQL Server with ProductSales Table Column

Now, suppose you have a task to create a simple text-based chart to represent the sales of different products. For that, you can use the below query.

SELECT ProductName,
   	REPLICATE('*', Sales / 10) AS SalesBar
FROM ProductSales;

REPLICATE Function of SQL Server with Table Column

As you can see in the above output, an asterisk-based horizontal bar is created for each product that represents the sales of that product. As you can see, Bluetooth Speaker has more sales than other products.

Let’s understand the query part REPLICATE(‘*’, Sales / 10) AS SalesBar;

Sales/10 is the operation that divides the values in the Sales column by 10. As you know, the Sales column contains integer values; this division will reduce the sales figure to a tenth of its original value.
Then the REPLICATE(‘*’, reduced_to_tenth_value), after the REPLICATE function, takes that reduced value and replicates the specified string character ‘*’, which is an asterisk in this case that many times.
For example, if you pass the sales as 100 to the function as REPLICATE(‘*’, 100/10), then after division operation, it becomes REPLICATE(‘*’,10), and then replicates the character ‘*’ ten times as **********.

In the same way, the REPLICATE() function is applied on each value of the Sales column and then replicates the asterisk character that many times.

Instead of an asterisk (*), you can use any string character such as #, – etc. You can use the REPLICATE function in SQL Server to represent data visually.

Let’s take one more example, where you will use the REPLICATE() function to generate the data.

First, create a SQLTable table where you will store the generated data.

CREATE TABLE SQLTable (
	GeneratedData VARCHAR(MAX)
);

If you want to generate the string ‘SQLTesting’ for performance testing based on the number of rows in another table, use the query below.

INSERT INTO SQLTable (GeneratedData)
SELECT REPLICATE('SQLTesting', 1000)
FROM Employees;

REPLICATE Function of SQL Server Generating Data

Now, view the SQLTable table using the query below.

SELECT * FROM SQLTable;

REPLICATE Function of SQL Server Generating Data For Testing

As you can see in the above output, the SQLtable contains 20 records, each containing the string ‘SQLTesting’ 1000 times.

Twenty records are created because, in the Employees table, there are only 20 rows. This is how to generate data using the REPLICATE function in SQL Server.

Remember, using the REPLICATE() function with massive data can impact or slow down performance and memory usage.

Conclusion

In this SQL Server tutorial, you covered how to replicate the given string character repeatedly using the REPLICATE() function in SQL Server. You applied the REPLICATE() function literal string and table columns. While using the REPLICATE() function with columns, you learned how to represent the data in a visual format.