REPLICATE#
Repeats a string value a specified number of times.
Syntax#
REPLICATE ( string_expression, integer_expression )
Arguments#
string_expression
Is an expression of a character string or binary data type. string_expression can be a constant, variable, or column either character or binary data. A binary value, or a value of another type, is converted to varchar before it is repeated.
integer_expression
Is an expression of any integer type, including bigint. If integer_expression is negative, NULL is returned.
Return types#
Returns nvarchar when string_expression is nchar or nvarchar; otherwise returns varchar. The result is varchar(max) or nvarchar(max) when string_expression is a max type. Otherwise its length is the length of string_expression times integer_expression when integer_expression is an int constant, and 8,000 bytes in every other case.
Remarks#
If string_expression is not of type varchar(max) or nvarchar(max), the result is truncated at 8,000 bytes:
8,000 varchar or 4,000 nvarchar characters. Only complete repetitions are kept, so REPLICATE('abc', 3000)
returns 7,998 characters. To return a longer value, convert string_expression to a max type explicitly.
If integer_expression is negative, the result is NULL.
When a data source executes the function, Querona sends it an expression that applies the same limit and returns NULL for a negative count, so the result does not depend on where the function runs. The data source still applies its own string limits: Oracle returns at most 4,000 bytes. Db2 and Vertica cannot convert a binary string_expression to a character string. A connection that uses a configurable SQL dialect sends the function through the dialect’s SQL template, which Querona cannot extend with the limit: it returns NULL for a negative count, and the data source returns the whole value, typed as varchar(max) or nvarchar(max).
Examples#
SELECT REPLICATE('no more sweets ', 3);
The following example returns 10,000 characters, because string_expression is converted to varchar(max) first.
SELECT LEN(REPLICATE(CAST('ab' AS varchar(max)), 5000));