REPLICATE Function in SQL Server   Leave a comment

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



Remove the padded data

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




Posted October 3, 2014 by Izharuddin Shaikh in SQL

Tagged with

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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

%d bloggers like this: