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