SQL Server - Replicate Function

SQL Server "Replicate" Function repeats a character expression to a specified number of times. It is equivalent to or alternative of Oracle's RPAD function.

Let's' check how this will be useful to the developers.

SELECT LEN(REPLICATE(2, '12'))

The above query will return 12 as a result due to 2 will be called 12 times (222222222222) hence the string length will be 12.

SELECT REPLICATE(2, 5)

The above statement will place 2 five times hence the result will be 22222.

SELECT REPLICATE('SQL', 5)

The above statement will place 'SQL' five times hence the result will be 'SQLSQLSQLSQLSQL'.

Let's' go through with more examples.

/**********/

CASE-1

/**********/

DECLARE @String AS NUMERIC(8,2)

SELECT @String = .2

SELECT @String

The above example returns 0.20 as a result. Assume that there is a requirement to have a 4 digit string (fixed length) like '00.20' but not just 0.20. Though logically this requirement is inappropriate but just think of the format.

/**********/

CASE-2

/**********/

DECLARE @String AS NUMERIC(8,2)

SELECT @String = .02

SELECT @String

The above statement returns 0.02 as a result. Similar to above case, requirement is to have 00.02.

Let's' now use the 'Replicate' function to get the required result for the above said two cases.

DECLARE @String AS NUMERIC(8,2)

SELECT @String = .2

SELECT CAST(REPLICATE(0, 1) AS VARCHAR(5)) + Cast(@String AS VARCHAR(5))

Or you can use the following instead

SELECT CAST(REPLICATE(0, Len(@String)-3) AS VARCHAR(5)) + Cast(@String AS VARCHAR(5))

The above query will return 00.20 as a result.

We have provided '.2' but SQL Server will store the number as 0.20 due to the datatype what we have provided. Hence the total string length will be 4 (along with the period).

SELECT CAST(REPLICATE(0, Len(@String)) AS VARCHAR(5)) + Cast(@String AS VARCHAR(5))

The result will be 00000.20, since we have requested to place the 0 for 4 times (lengh of the string is 4)

DECLARE @String AS NUMERIC(8,2)

SELECT @String = 08.2

SELECT Cast(Replicate(0,5-Len(@String)) AS VARCHAR(5)) + Cast(@String AS VARCHAR(5))

The result will be 08.20 for the above statement due to we had commanded that the length of the string will be 5 and out of five we have provided 4 characters to the variable @String.

Let's' write a stored procedure to avoid providing length parameter manually.

/******************** Procedure **********************/

CREATE PROCEDURE uspReplicate(@String AS NUMERIC(8,2))

AS

DECLARE

@length AS INT

BEGIN

SET @length = LEN(@String)

IF @length < 5

BEGIN

SELECT CAST(REPLICATE(0, 5-@length) AS VARCHAR(10)) + Cast(@String AS VARCHAR(10))

END

ELSE

SELECT @String

END

/************* End of the Procedure *****************/

EXEC uspReplicate 0.02

Result : 00.02

EXEC uspReplicate .02

Result : 00.02

EXEC uspReplicate .2

Result : 00.20

EXEC uspReplicate 2.02

Result : 02.02

EXEC uspReplicate 2.0

Result : 02.00

EXEC uspReplicate 2

Result : 02.00

The below example will let you know the real use of this function.

DECLARE @Account1 INT='6700'

DECLARE @Account2 INT='007800'

DECLARE @Account3 INT='108978'

SELECT @Account1, @Account2, @Account3

If you look at the values, the account number should be 6 digit value. But when you retrieve them, the returned values are 6700, 7800 and 108978. To bring all of them in proper 6 digit format, use the below code.

SELECT CONCAT(REPLICATE('0', 6-LEN(@Account1)),@Account1)

SELECT CONCAT(REPLICATE('0', 6-LEN(@Account2)),@Account2)

SELECT CONCAT(REPLICATE('0', 6-LEN(@Account3)),@Account3)

Hope this article helped you in understanding the usage of replicate function.