Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

REPLICATE (Transact-SQL)

Repeats a string value a specified number of times.

Topic link icon Transact-SQL Syntax Conventions


REPLICATE ( string_expression ,integer_expression ) 

string_expression

Is an expression of a character string or binary data type. string_expression can be either character or binary data.

ms174383.note(en-US,SQL.90).gifNote:
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

integer_expression

Is an expression of any integer type, including bigint. If integer_expression is negative, NULL is returned.

Returns the same type as string_expression.

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

A. Using REPLICATE

The following example replicates a 0 character four times in front of a production line code in the AdventureWorks database.

USE AdventureWorks
GO
SELECT [Name]
, REPLICATE('0', 4) + [ProductLine] AS 'Line Code'
FROM [Production].[Product]
WHERE [ProductLine] = 'T'
ORDER BY [Name]
GO

Here is the result set.

Name                                               Line Code
-------------------------------------------------- ---------
HL Touring Frame - Blue, 46                        0000T 
HL Touring Frame - Blue, 50                        0000T 
HL Touring Frame - Blue, 54                        0000T 
HL Touring Frame - Blue, 60                        0000T 
HL Touring Frame - Yellow, 46                      0000T 
HL Touring Frame - Yellow, 50                      0000T
...

B. Using REPLICATE and DATALENGTH

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

USE AdventureWorks;
GO
IF EXISTS(SELECT name FROM sys.tables
      WHERE name = 't1')
   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

Community Additions

ADD
Show:
© 2015 Microsoft