REPLICATE String Function¶

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.

Sample Uses of the REPLICATE String Function¶

Here are sample uses of the REPLICATE string function

Usage #1 : Right-Align Text¶

DECLARE @Header1 VARCHAR(25) = 'Database 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
---------------
                              Database Helper, LLC
                                   1234 1st Street
                          Somewhere City, NY 01001

Usage #2 : Center-Align Text¶

DECLARE @Header1 VARCHAR(25) = 'Database 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
------------
               Database 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