Transact-SQL Reference

REPLICATE

Repeats a character expression for a specified number of times.

Syntax

REPLICATE ( character_expression , integer_expression )

Arguments

character_expression

Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

integer_expression

Is a positive whole number. If integer_expression is negative, a null string is returned.

Return Types

varchar

character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to convert explicitly character_expression.

Remarks

Compatibility levels can affect return values. For more information, see sp_dbcmptlevel.

Examples
A. Use REPLICATE

This example replicates each author's first name twice.

USE pubs
SELECT REPLICATE(au_fname, 2) 
FROM authors
ORDER BY au_fname

Here is the result set:

---------------------- 
AbrahamAbraham                           
AkikoAkiko                               
AlbertAlbert                             
AnnAnn                                   
AnneAnne                                 
BurtBurt                                 
CharleneCharlene                         
CherylCheryl                             
DeanDean                                 
DirkDirk                                 
HeatherHeather                           
InnesInnes                               
JohnsonJohnson                           
LiviaLivia                               
MarjorieMarjorie                         
MeanderMeander                           
MichaelMichael                           
MichelMichel                             
MorningstarMorningstar                   
ReginaldReginald                         
SherylSheryl                             
StearnsStearns                           
SylviaSylvia                             
(23 row(s) affected)
B. Use REPLICATE, SUBSTRING, and SPACE

This example uses REPLICATE, SUBSTRING, and SPACE to produce a telephone and fax listing of all authors in the authors table.

-- Replicate phone number twice because the fax number is identical to 
-- the author telephone number.
USE pubs
GO
SELECT SUBSTRING((UPPER(au_lname) + ',' + SPACE(1) + au_fname), 1, 35) 
   AS Name, phone AS Phone, REPLICATE(phone,1) AS Fax
FROM authors
ORDER BY au_lname, au_fname
GO

Here is the result set:

Name                                Phone        Fax                    
----------------------------------- ------------ -----------------------
BENNET, Abraham                     415 658-9932 415 658-9932           
BLOTCHET-HALLS, Reginald            503 745-6402 503 745-6402           
CARSON, Cheryl                      415 548-7723 415 548-7723           
DEFRANCE, Michel                    219 547-9982 219 547-9982           
DEL CASTILLO, Innes                 615 996-8275 615 996-8275           
DULL, Ann                           415 836-7128 415 836-7128           
GREEN, Marjorie                     415 986-7020 415 986-7020           
GREENE, Morningstar                 615 297-2723 615 297-2723           
GRINGLESBY, Burt                    707 938-6445 707 938-6445           
HUNTER, Sheryl                      415 836-7128 415 836-7128           
KARSEN, Livia                       415 534-9219 415 534-9219           
LOCKSLEY, Charlene                  415 585-4620 415 585-4620           
MACFEATHER, Stearns                 415 354-7128 415 354-7128           
MCBADDEN, Heather                   707 448-4982 707 448-4982           
O'LEARY, Michael                    408 286-2428 408 286-2428           
PANTELEY, Sylvia                    301 946-8853 301 946-8853           
RINGER, Albert                      801 826-0752 801 826-0752           
RINGER, Anne                        801 826-0752 801 826-0752           
SMITH, Meander                      913 843-0462 913 843-0462           
STRAIGHT, Dean                      415 834-2919 415 834-2919           
STRINGER, Dirk                      415 843-2991 415 843-2991           
WHITE, Johnson                      408 496-7223 408 496-7223           
YOKOMOTO, Akiko                     415 935-4228 415 935-4228           
(23 row(s) affected)
C. Use REPLICATE and DATALENGTH

This example left pads numbers to a specified length as they are converted from a numeric data type to character or Unicode.

USE Northwind
GO
DROP TABLE t1
GO
CREATE TABLE t1 
(
 c1 varchar(3),
 c2 char(3)
)
GO
INSERT INTO t1 VALUES ('2', '2')
INSERT INTO t1 VALUES ('37', '37')
INSERT INTO t1 VALUES ('597', '597')
GO
SELECT REPLICATE('0', 3 - DATALENGTH(c1)) + c1 AS [Varchar Column],
       REPLICATE('0', 3 - DATALENGTH(c2)) + c2 AS [Char Column]
FROM t1
GO

See Also

Data Types

String Functions