How to insert a string or a character value for n number of times?

Problem: How to insert a string or a character value for n number of times?

Solution: REPLICATE Starting SQL Server 2008.

In some cases we would like to pad the input values up to a length for showing the consistent values in that cases you can use the replicate function for adding zeros in the front.

Syntax: REPLICATE ( string_expression ,integer_expression ) 

Example:

In the example, I would like to show we can pad zeros to a value based on the input data length.

IF EXISTS(SELECT name FROM sys.tables 

      WHERE name = 'ReplicateTable') 

   DROP TABLE ReplicateTable; 

GO 

CREATE TABLE ReplicateTable  

( 

 col1 varchar(4), 

 col2 char(4) 

); 

GO 

INSERT INTO ReplicateTable VALUES ('2', '2'), ('37', '37'),('597', '597'); 

GO

--Test Replicate

SELECT REPLICATE('0', 4 - DATALENGTH(col1)) + col1 AS 'Varchar Column', 

       REPLICATE('0', 4 - DATALENGTH(col2)) + col2 AS 'Char Column' 

FROM ReplicateTable; 

GO 

OUTPUT:

Varchar Column	Char Column
0002	2
0037	37
0597	597

As we can zeros are padded in the varchar column before each value string.