The REPLICATE string function returns a string value a specified number of times. The syntax of the REPLICATE string function is as follows:
REPLICATE ( < string_expression >, < integer_expression > )
The < string_expression > is an expression of a character string or binary data type. The < integer_expression > is an expression of any integer type, including BIGINT. If < integer_expression > is negative, a NULL string is returned.
Here are sample uses of the REPLICATE string function
Usage #1 : Right-Align Text
DECLARE @Header1 VARCHAR(25) = 'SQL Server Helper, LLC'
DECLARE @Header2 VARCHAR(25) = '1234 1st Street'
DECLARE @Header3 VARCHAR(25) = 'Somewhere City, NY 01001'
PRINT REPLICATE(' ', 50 - LEN(@Header1)) + @Header1
PRINT REPLICATE(' ', 50 - LEN(@Header2)) + @Header2
PRINT REPLICATE(' ', 50 - LEN(@Header3)) + @Header3
Output
---------------
SQL Server Helper, LLC
1234 1st Street
Somewhere City, NY 01001
Usage #2 : Center-Align Text
DECLARE @Header1 VARCHAR(25) = 'SQL Server Helper, LLC'
DECLARE @Header2 VARCHAR(25) = '1234 1st Street'
DECLARE @Header3 VARCHAR(25) = 'Somewhere City, NY 01001'
PRINT REPLICATE(' ', (50 - LEN(@Header1))/2) + @Header1
PRINT REPLICATE(' ', (50 - LEN(@Header2))/2) + @Header2
PRINT REPLICATE(' ', (50 - LEN(@Header3))/2) + @Header3
Output
------------
SQL Server Helper, LLC
1234 1st Street
Somewhere City, NY 01001
Usage #3 : Justify Text
DECLARE @Contents TABLE (
[Title] VARCHAR(50),
[PageNumber] INT
)
INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Introduction', 1)
INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Table of Contents', 2)
INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Index', 100)
SELECT [Title] + REPLICATE(' ', 50 - LEN([Title])) +
RIGHT(REPLICATE(' ', 5) + CAST([PageNumber] AS VARCHAR(5)), 5) AS [Output]
FROM @Contents
Output
------------
Introduction 1
Table of Contents 2
Index 100