Replicate is the expression of repeated characters for a specified number of times.

Replicate

Returns the expression of repeated characters of a specified number of times.

Syntax

Replicate(Character_expression, Integer_expression)

Parameters

Character_expression

A alphanumeric expression consisting of character data.Character_expressionIt can be a constant, a variable, a character column, or a binary data column.

Integer_expression

Is a positive integer. IfInteger_expressionIf it is negative, an empty string is returned.

Return type

Varchar

Character_expressionMust be implicitly convertedVarchar. Otherwise, use the cast function for digital conversion.Character_expression.

Note

The compatibility level may affect the return value. For more information, see Sp_dbcmptlevel.

Example A. Use replicate

The following example repeats the name of each author twice.

 
Use pubsselect replicate (au_fname, 2) from authorsorder by au_fname
 

The following is the result set:

 commandid describertalbert annann anneanne ~~cherylcheryl deandean ~heatherheather innesinnes johnsonjohnson livialivia ~~~~michaelmichael Michelin ~~~sherylsheryl ~sylviasylvia (23 row (s) affected)

B. Use replicate, substring, and space

The following example uses replicate, substring, and space to generateAuthorsThe telephone and fax lists of all authors in the table.

 
-- Replicate phone number twice because the fax number is identical to -- the author telephone number. use pubsgoselect substring (upper (au_lname) + ',' + space (1) + au_fname), 1, 35) as name, phone as phone, replicate (phone, 1) as faxfrom authorsorder by au_lname, au_fnamego
 

The following is the result set:

Name phone fax ----------------------------------- ----------------------------- Bennet, Abraham 415 658-9932 415-658 BLOTCHET-HALLS, Reginald 9932-503 745-6402 Carson, cheryl 415 548-7723 415-548 defrance, micel 7723 219-547 9982-219 del Castillo, Innes 547 9982-615 996-8275 dull, ann 415 836-7128 415 836-7128 green, Marjorie 415 986-7020 415-986 Greene, Morningstar 7020 615-297 2723-615 gringlesby, burt 707 938-6445 707-938 Hunter, Sheryl 6445 415-836 7128-415 Karsen, Livia 836 7128-415 534 9219-415 Locksley, charlene 415 585-4620 415-585 macfeather, Stearns 4620 415-354 7128-415 mcbadden, Heather 354 7128-707 448 4982-707 O 'Leary, michael 408 286-2428 408-286 panteley, Sylvia 2428 301-946 8853-301 ringer, Albert 946 8853-801 826-0752 ringer, anne 801 826-0752 801 826-0752 Smith, meander 913 843-0462 913-843 straight, Dean 0462 415-834 2919-415 Stringer, dirk 415 843-2991 415-843 white, Johnson 2991 408-496 7223-408 yokomoto, Akiko 496 7223-415 935-4228 (23 row (s) affected)

C. Use replicate and datalength

In this example, when a value is converted from a numeric data type to a numeric or Unicode type, the number is filled from the left to reach the specified length.

 
Use northwindgodrop table t1gocreate table T1 (C1 varchar (3), C2 char (3) goinsert into T1 values ('2', '2 ') insert into T1 values ('37', '37') insert into T1 values ('000000', '000000') goselect replicate ('0', 3-datalength (C1 )) + C1 as [varchar column], replicate ('0', 3-datalength (C2) + C2 as [char column] From t1go