REPLICATE Function in SQL Server

Replicate function is used to repeat the string / pattern to fix number of times.

Let’s take a simple example to understand it. Lets say for formatting of data or to beautify the data you want to put some pattern of string in the beginning and end of the data. Let’s say we want to generate the below shown data.

**********It is WhiteX technical blog**********

Simple way using Replicate function is

SELECT REPLICATE(‘*****’, 2) + ‘It is WhiteX technical blog’ +  REPLICATE(‘*****’, 2) As Pattern

Other use of replicate function is when we do the padding of Zeros with financial elements. You can achieve the same using below SQL.

Declare @Number Int = 1234
SELECT REPLICATE(‘0’,6-LEN(@Number)) + Cast(@Number As varchar) As NumberWithPadding

Output:

NumberWithPadding
001234

Remove the padded data

Declare @Number varchar(10) = ‘001234’
SELECT (RIGHT(@Number,(LEN(@Number) – PATINDEX(‘%[^0]%’,Cast(@Number As varchar))) + 1)) As NumberWithRemovedPadding

Output:

NumberWithRemovedPadding
1234

Advertisements

#replicate-function