REPLICATE (Transact-SQL)
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
Syntax
REPLICATE ( string_expression , integer_expression )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
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.
Return Types
Returns the same type as string_expression.
Examples
A. Using REPLICATE
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 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)
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
C: Using REPLICATE
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
See Also
SPACE (Transact-SQL)
Data Types (Transact-SQL)
String Functions (Transact-SQL)
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru