TSQL REPLICATE Command Usage and Examples: A Comprehensive Guide

TSQL or Transact SQL is an extended version of standard SQL (Structured Query Language) which is developed for interactions with relational databases. TSQL deals with a lot of additional features and functions that are not included in standard SQL. One of the primary features of TSQL is that it includes procedural programming and offers multiple support functions that help to deal with data processing, string processing, mathematics, etc. In this tutorial, you will be learning about one such function – the TSQL REPLICATE () function.

This article covers the following –

• TSQL REPLICATE Command – Need
• TSQL REPLICATE Command – Usage
• TSQL REPLICATE Command – Examples and Explanations
• Using REPLICATE() to Format Text Alignment
• REPLICATE Command Alternative in MYSQL
• Points to Note
• Conclusion

TSQL REPLICATE Need

REPLICATE is an inbuilt string function used in TSQL that is used to repeat a given string for a specified number of times. The function accepts both character string and binary data type as the input and the return type is the same as that of the expression. The Transact SQL Replicate function can be used in SQL Server (supported versions), Parallel Data Warehouse, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.

TSQL REPLICATE Command – Usage

The Replicate command follows the below template.

REPLICATE (string_input , count)

In the above template, the string_input can be a character string or a binary data type while the count is an integer type expression which denotes the number of times you want to repeat the string_input. The return type of the function is the same as that of the string_input.

TSQL REPLICATE Command – Examples and Explanations

Here are some common use cases of the REPLICATE command followed by an explanation of the output. Study these cases to learn how REPLICATE() works.

Use Case 1

DECLARE @String VARCHAR(50)
SET @String = 'Use TSQL Replicate function'
SELECT REPLICATE (@String, 3) AS 'Use TSQL Replicate function'

Output

Use TSQL Replicate functionUse TSQL Replicate functionUse TSQL Replicate function

Explanation

The above is a very basic example of the replicate function. Here, the @string data ‘Use TSQL Replicate function’, is repeated for 3 times and shown in the output.

Use Case 2

DECLARE @String VARCHAR(50)
SET @String = 'Use TSQL Replicate function'
SELECT REPLICATE ((@String + ', '), 3) AS 'Use TSQL Replicate function'

Output

Use TSQL Replicate function, Use TSQL Replicate function, Use TSQL Replicate func..

Explanation

In this example, the arithmetic operator ‘+’ is used to separate the repeated items with a ‘,’ and space.

Use Case 3

SELECT REPLICATE ('Use Replicate function, ', 3) AS 'Replicate Result'

Output

Use Replicate function, Use Replicate function, Use Replicate func..

Explanation

In the above example, we see how the string data is used directly inside the Transact SQL replicate function and the statement repeats the string 3 times in the output all separated by the delimiter as specified in the query.

Use Case 4

Using the REPLICATE function to define the serial number of products in a table

To show this example, we will use the below table.

ProductName	Color	StandardCost	TaxAmount
Aniseed Syrup	Blue	414	50
Tofu	Beige	650	65
Vanilla Biscuits	White	500	58

TSQL Query

SELECT [ProductName] ,[Color] ,LEFT([ProductName], 2) + REPLICATE('0', 3) + RIGHT([ProductName], 2) AS 'Product Serial' ,[StandardCost] ,[TaxAmount]
FROM [SQL TUTORIAL].[ProductDetail]

Output Data

ProductName	Color	ProductSerial	Standard Cost	TaxAmount
Aniseed Syrup	Blue	An000up	414	50
Tofu	Beige	Be000ge	650	65
Vanilla Biscuits	White	Wh000te	500	58

Explanation

In the above query, the LEFT function returns the leftmost two characters from the ProductName column. The RIGHT function returns the rightmost 2 characters from the ProductName column. And the REPLICATE function is used here to replicate ‘0’ 3 times.

The arithmetic operator is used in the query to concatenate the result of the 3 statements. Thus the final value of the product serial number becomes leftmost 2 characters of ProductName + three 0s + rightmost 2 characters of ProductName.

Use Case 5

Using REPLICATE to append zero to column data to get desired length.

Below is an EMPLOYEE table. We will be considering this table to establish the above case.

Emp ID	Emp Name	Place	Div
110	Alex	New York	A
2100	Clara	Wales	A
310	Dean	Texas	C
410	Samuel	Rome	B
5100	Ruby	New Jersey	C

The requirement is to update EMP ID to 5 characters long. Thus we have to add zeroes as needed to update the EMP ID column and make the resultant value 5 characters long.

Query

SELECT CONCAT (REPLICATE (‘0’, 5-LEN (Emp_id)), Emp_id) AS Emp_id, Emp_name, City, State, Salary FROM EMPLOYEE;

Output

Emp ID	Emp Name	City	State	Div
00110	Alex	Pune	New York	A
02100	Clara	Jaipur	Wales	A
00310	Dean	Mumbai	Texas	C
00410	Samuel	Allahabad	Rome	B
05100	Ruby	Kolkata	New Jersey	C

Explanation

Here, the LEN function is used to define the required length and thus it calculates the number of zeros that are needed to be padded. The REPLICATE function defines the zeros that are to be replicated. Finally, the CONCAT function is used here to concatenate the results returned by these two functions. Thus in the output, we see that the employee ID has zeroes left padded and the resultant value is 5 characters long.

Using REPLICATE() to Format Text Alignment

From the above use cases, you have learned about the various ways in which you can use the REPLICATE command to repeat an input string. The REPLICATE command can also be used to align texts. In the following section, we will see how the REPLICATE command is used to Justify, center-align, or right-align texts.

Justify Text using REPLICATE()

Query

DECLARE @Contents TABLE ( [Topic] VARCHAR(50), [PageNumber] INT)
INSERT INTO @Contents ( [Topic], [PageNumber] )VALUES ( Abstract, 1)
INSERT INTO @Contents ( [Title], [PageNumber] )VALUES ( 'Table of Contents', 2)
INSERT INTO @Contents ( [Title], [PageNumber] )VALUES ( References, 100)
SELECT [Topic] + REPLICATE(' ', 50 - LEN([Topic])) + RIGHT(REPLICATE(' ', 5) + CAST([PageNumber] AS VARCHAR(5)), 5) AS [Output]FROM @Contents

Output

Output ———— Abstract 1 Table of Contents 2 References 100

Centre Align Text using REPLICATE ()

DECLARE @Header1 VARCHAR(25) = 'Centre Align LLC'
DECLARE @Header2 VARCHAR(25) = 'Some Street 1234t'
DECLARE @Header3 VARCHAR(25) = 'Some City, NY 01001'PRINT REPLICATE(' ', (50 - LEN(@Header1))/2) + @Header1
PRINT REPLICATE(' ', (50 - LEN(@Header2))/2) + @Header2
PRINT REPLICATE(' ', (50 - LEN(@Header3))/2) + @Header3

Output

Output ———— Centre Align LLC Some Street 1234t Some City, NY 01001

Right Align Text Using Replicate ()

DECLARE @Header1 VARCHAR(25) = Right Align LLC'
DECLARE @Header2 VARCHAR(25) = 'Some Street 1234t'
DECLARE @Header3 VARCHAR(25) = 'Some City, NY 01001'
PRINT REPLICATE(' ', 50 - LEN(@Header1)) + @Header1
PRINT REPLICATE(' ', 50 - LEN(@Header2)) + @Header2
PRINT REPLICATE(' ', 50 - LEN(@Header3)) + @Header3

Output

Output ———— Right Align LLC Some Street 1234t Some City, NY 01001

REPLICATE Command Alternative in MySQL

Although the Replicate function works in MS SQL and Azure SQL it has its own limitations. REPLICATE() does not work in one of the most widely used open-source DBMS, MySQL. However, if you are using MySQL, you can use the REPEAT() as an alternative to the REPLICATE command. The REPEAT function does the exact work as REPLICATE in MS SQL.

Points to Note

Now that you have learned the various ways in which REPLICATE command can be used in TSQL, before ending this guide, it is essential to remember a few points when you pass arguments inside the REPLICATE function.

For cases when string_input type is binary, the REPLICATE function performs an absolute conversion to varchar and hence doesn’t keep the binary input.

For cases when string_input type is varchar(max) or nvarchar(max), the REPLICATE function truncates the return value to 8000 bytes. If you want to return a value that is greater than 8000 bytes, use a large value data type for string_input.

Count is an integer type expression that supports BIGINT as well.

If you pass a negative integer type in count, NULL value is returned.

Conclusion

In this article, you have learned in detail about the TSQL REPLICATE command. You have seen its purpose along with several use cases and examples.

When it comes to fully managed ETL, you can’t find a better solution than Hevo. It is a No-code Data Pipeline that will help you move data from multiple data sources to your destination. It is consistent and reliable. It has pre-built integrations from 100+ sources. So, give it a try, Sign Up Now!

Share your thoughts on the TSQL REPLICATE command in the comments below. We would love to hear from you!

Visit our Website to Explore Hevo