Events
Apr 8, 3 PM - May 28, 7 AM
Sharpen your AI skills and enter the sweepstakes to win a free Certification exam
Register now!This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Repeats a string value a specified number of times.
Transact-SQL syntax conventions
REPLICATE ( string_expression , integer_expression )
string_expression
Is an expression of a character string or binary data type.
Note
If string_expression is of type binary, REPLICATE will perform an implicit conversion to varchar, and therefore will not preserve the binary input.
Note
If string_expression input 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.
The following example replicates a 0
character four times in front of a production line code in the AdventureWorks2022 database.
SELECT [Name]
, REPLICATE('0', 4) + [ProductLine] AS 'Line Code'
FROM [Production].[Product]
WHERE [ProductLine] = 'T'
ORDER BY [Name];
GO
Here's 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
...
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's the result set.
Varchar Column Char Column
-------------------- ------------
002 2
037 37
597 597
(3 row(s) affected)
The following example replicates a 0
character four times in front of an ItemCode
value.
-- Uses AdventureWorks
SELECT EnglishProductName AS Name,
ProductAlternateKey AS ItemCode,
REPLICATE('0', 4) + ProductAlternateKey AS FullItemCode
FROM dbo.DimProduct
ORDER BY Name;
Here are the first rows in the result set.
Name ItemCode FullItemCode
------------------------ -------------- ---------------
Adjustable Race AR-5381 0000AR-5381
All-Purpose Bike Stand ST-1401 0000ST-1401
AWC Logo Cap CA-1098 0000CA-1098
AWC Logo Cap CA-1098 0000CA-1098
AWC Logo Cap CA-1098 0000CA-1098
BB Ball Bearing BE-2349 0000BE-2349
SPACE (Transact-SQL)
Data Types (Transact-SQL)
String Functions (Transact-SQL)
Events
Apr 8, 3 PM - May 28, 7 AM
Sharpen your AI skills and enter the sweepstakes to win a free Certification exam
Register now!