SQL Server REPLICATE Function


By:

The REPLICATE function is used to replicate a string a specified number of times.

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
t-sql replicate hello world 2 times

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
t-sql create dummy data

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 t-sql function with numbers

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 sql server function with null values

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]
replicate example t-sql 5 digits

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]
mask part of value with replicate

Related Articles


Last Update: 11/10/2021




Comments For This Article





download














get free sql tips
agree to terms