SQL SERVER REPLICATE STRING FUNCTION

SQL SERVER REPLICATE STRING FUNCTION

Replicate String function - Repeats a string value n number of times

Replicate(StringToRepeat,nTimes)

Ex: Repeat 100 5 times in T-SQL

select replicate(100,5) [100*5]

O/P:

100*5
100100100100100

Ex:Display Integers in 3 digit format using REPLICATE function

declare @i smallint,@j smallint
set @i=1
set @j=2
select concat(replicate('0',3-len(@i)),@i) i,concat(replicate('0',3-len(@j)),@j) j

O/P:

i    j
001    002

Note: Same thing can be applied to any table column.

Ex:Convert String to formatted Data  using REPLICATE(LEFT SIDE)

declare @custCode varchar(5)
set @custCode='123'
select concat(REPLICATE('#',5-datalength(@custCode)),@custCode) customerCode

O/P:

customerCode
##123

Ex:Convert String to formatted Data  using REPLICATE(RIGHT SIDE)

declare @custCode varchar(5)
set @custCode='123'
select concat(@custCode,REPLICATE('#',5-datalength(@custCode))) customerCode

O/P:

customerCode
123##

Tags:SQL SERVER REPLICATE STRING FUNCTION,REPEAT NUMBER IN SQL SERVER,REPLICATE IN SQL SERVER,STRING CONCAT IN SQL-SERVER,DISPLAY INTEGERS IN FORMATTED OUTPUT,DISPLAY CUSTOMEr CODE IN FORMATTED OUTPUT.