TSQL REPLICATE Command Usage and Examples: A Comprehensive Guide

on Tutorials • August 28th, 2020 • Write for Hevo

TSQL REPLICATE

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 –

Hevo, A Simpler Alternative to Integrate your Data for Analysis

Hevo offers a faster way to move data from databases or SaaS applications into your data warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Get Started with Hevo for Free

Check out some of the cool features of Hevo:

Hevo Data
  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support call.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.

You can try Hevo for free, Sign up here for a 14-Day Free Trial!

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.

ProductNameColorStandardCostTaxAmount
Aniseed SyrupBlue41450
TofuBeige65065
Vanilla BiscuitsWhite50058

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

ProductNameColorProductSerialStandard CostTaxAmount
Aniseed SyrupBlueAn000up41450
TofuBeigeBe000ge65065
Vanilla BiscuitsWhiteWh000te50058

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 IDEmp NamePlaceDiv
110AlexNew YorkA
2100ClaraWalesA
310DeanTexasC
410SamuelRomeB
5100RubyNew JerseyC

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 IDEmp NameCityStateDiv
00110AlexPuneNew YorkA
02100ClaraJaipurWalesA
00310DeanMumbaiTexasC
00410SamuelAllahabadRomeB
05100RubyKolkataNew JerseyC

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

No-code Data Pipeline for your Data Warehouse