Overview
The REPLICATE function is used to replicate a string a specified number of times.
Explanation
Syntax
REPLICATE(expression, numberOfRepetitions)Parameters
- expression – this is the character string or expression to be replicated.
- numberOfRepetitions – the number of times that we want to repeat the expression.
Simple REPLICATE Example
The following example, shows how to replicate the “Hello world” string twice.
SELECT REPLICATE('Hello world', 2) as msg
Dummy Text Generator Example Using REPLICATE
Sometimes, we need dummy text for testing purposes. With the REPLICATE function you can generate thousand of characters very quickly.
The following example will create a table with a nvarchar(max) and then we will insert a string of 57000 characters (19 characters repeated 3000 times) using the REPLICATE function.
CREATE TABLE longtext (doc nvarchar(max))
INSERT INTO longtext VALUES (REPLICATE('This is a long text', 3000))
SELECT doc FROM longtext
REPLICATE Function for Numbers
In this example, we will replicate the number “2” eight times. As you can see, the function works not only with character strings, but with numbers as well.
SELECT REPLICATE(2,8) as msg
REPLICATE Function with NULL Values
If a NULL value is used as a parameter, the function will return NULL as output.
SELECT REPLICATE(NULL,8) as msg
REPLICATE Function with Fixed Number of Digits
The following example uses the AdventureWorks database and will add zeros to the left if the number provided does not have 5 digits. The function will detect the length of the number and calculate the number of zeros to complete the 5 digits.
SELECT BusinessEntityID,
REPLICATE('0', 5 - LEN(BusinessEntityID)) + CONVERT(nvarchar(30),BusinessEntityID) as output
FROM [HumanResources].[Employee]
Use REPLICATE Function to Mask Part of String
Here is another example that uses the AdventureWorks database to mask the beginning part of a value using asterisks.
SELECT AccountNumber,
REPLICATE('*', 7) + RIGHT(AccountNumber,3) as output
FROM [Sales].[Customer]
Additional Information
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
