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

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 )

Google+ photo

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

Connecting to %s