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 is called 12 times (222222222222) hence the string length is 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, values that get returned 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.

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s