MS SQL Server REPLICATE() Function

Sometimes you may need to repeat a same string in multiple times in MS SQL Server. There is a function called REPLICATE in MS SQL Server by which you can do this. REPLICATE function inserts a string into another string. It repeats a string value a specified number of times.

Let’s see the function syntax:

REPLICATE (string_expression, integer_expression);

In this syntax:

• Parameter string_expression is an expression of a character string or binary data type.
• Parameter integer_expression is an expression of any integer type, including bigint.
• Function returns the same type as string_expression. If integer_expression is negative, NULL is returned.

The following example uses REPLICATE function to repeat same string multiple times:

SELECT LEFT(ac_no,5)+REPLICATE('x',5) as ac_no,ac_name,balance
FROM [DataAnalytics].[dbo].[ac_details]  
ORDER BY 1;  

Here is the result set.

In the above example REPLICATE function replicate 5 ‘x’ and added next with first five digit of a account number.

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to repeat a specific string multiple times using REPLICATE in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.