Export (0) Print
Expand All

REPLICATE (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Repeats a string value a specified number of times.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

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.

Note Note

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.

A. Using REPLICATE

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

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.

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'), ('37', '37'),('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

Here is the result set.

Varchar Column        Char Column
--------------------  ------------
002                   2  
037                   37 
597                   597

(3 row(s) affected)
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft