SQL REPLICATE Function 1

SQL REPLICATE Function

The SQL Server REPLICATE is a String Function used to repeat the existing string for a given number of times. This replicate function accepts both character string or binary data and returns the same type as the specified expression.

SQL Server REPLICATE Function

The syntax of the REPLICATE Function is:

SELECT REPLICATE (String_Expression, int_Expression)
FROM [Source]
  • String_Expression: Please specify the valid string. The Replicate function will repeat the string present in this String_Expression. It can be a character string or binary data.
  • int_Expression: How many characters do you want to repeat the String_Expression?.

SQL REPLICATE Function Example

The REPLICATE Function repeats the original string with a specified number of times. The following query will show multiple ways to use the Replicate function.

DECLARE @String VARCHAR(50)
SET @String = 'Learn SQL Server'

SELECT REPLICATE (@String, 3) AS 'SQLServer Replicate' 

SELECT REPLICATE ((@String + ', '), 3) AS 'SQLServer Replicate' 

--Replicating String Directly
SELECT REPLICATE ('Learn T-SQL Server ', 4) AS 'Replicate Result'
SQL REPLICATE Function 1

Here, We used the REPLICATE function to repeat the @String data for three times.

SELECT REPLICATE (@String, 3) AS 'SQLServer Replicate'

Next, we used the Arithmetic Operator to add the extra space between repeated items in SQL Server.

SELECT REPLICATE ((@String + ', '), 3) AS 'SQLServer Replicate'

In the next line, We used the string data directly inside the REPLICATE function, and the following statement will repeat ‘Learn T-SQL Server’ for 4 times

SELECT REPLICATE ('Learn T-SQL Server ', 4) AS 'Replicate Result'

REPLICATE Function Example 2

In this String Function example, we use the Replicate function to assign serial numbers to products. For this, We are going to use the below-shown data

Source Table 2

REPLICATE CODE

SELECT [EnglishProductName]
      ,[Color]
      ,LEFT([EnglishProductName], 2) +
	    REPLICATE('0', 3)  +
	    RIGHT([EnglishProductName], 2) AS 'Product Serial'
      ,[StandardCost]
      ,[ListPrice]
      ,[DealerPrice]
      ,[SalesAmount]
      ,[TaxAmt]
FROM [Product Sales]
SQL REPLICATE Function 3

In below statement, We used LEFT Function to return leftmost two characters from the [EnglishProductName] column data.

LEFT([EnglishProductName], 2)

The following statement will repeat 0 for 3 times.

REPLICATE('0', 3)

In the below statement, We used RIGHT Function to return the rightmost two characters from the [EnglishProductName] column data.

RIGHT([EnglishProductName], 2)

Lastly, we used the Arithmetic Operator to concatenate the Result of the above three statements. It means, First two characters of English Product Name + Three Zeros + Last two characters of Product Name.