REPLICATE (T-SQL)

Repeats a character expression 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 since the fax number is identical to

-- the author's 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