REPLICATE: Zero Copies and Negative Counts Differ

REPLICATE returns empty text for zero copies and NULL for a negative count. I keep those outcomes separate before building repeated text. A blank result in a query grid doesn’t explain which condition produced it.

Keep the source and repetition count visible

The first source value is AB with a count of three. Its expected result is ABABAB. A count of one retains AB once. Each copy repeats the entire source string rather than one selected character.

The query supplies a short varchar value and an explicit integer count. It keeps both inputs beside the generated text. That makes each result understandable without remembering the sample values. The final cast has enough capacity for every supplied output.

I don’t infer the requested count from the resulting display. An empty source can produce empty text even with a positive count. A missing source carries another meaning. Keeping the inputs prevents those cases from becoming indistinguishable.

WITH Inputs AS
(
    SELECT CaseId, SourceText, Copies
    FROM (VALUES
        (1, CAST('AB' AS varchar(4)), CAST(3 AS int)),
        (2, 'AB', 1), (3, 'AB', 0), (4, 'AB', -1),
        (5, 'AB', NULL), (6, NULL, 2), (7, '', 3), (8, ' ', 2)
    ) AS v(CaseId, SourceText, Copies)
), Repeated AS
(
    SELECT CaseId, SourceText, Copies,
           CAST(REPLICATE(SourceText, Copies) AS varchar(20)) AS RepeatedText
    FROM Inputs
)
SELECT CaseId, SourceText, Copies, RepeatedText,
       DATALENGTH(RepeatedText) AS OutputBytes,
       CASE WHEN RepeatedText IS NULL THEN 1 ELSE 0 END AS ResultIsNull
FROM Repeated
ORDER BY CaseId;

Zero copies produce a present empty value

Case three requests zero copies of AB. Its expected result is an empty string with zero bytes. ResultIsNull remains zero. The source was available, but the requested repetition produced no content.

That outcome can fit an optional display fragment. A caller asking for no repeated prefix doesn’t necessarily have invalid data. I’d still state whether zero is permitted. The function’s output doesn’t decide that business rule.

Case seven repeats an empty source three times. It also produces empty text with zero bytes. Its count differs from case three even though the outputs match. The original inputs preserve that distinction.

A negative count produces NULL

A negative count returns NULL. Case four therefore differs from the zero-count case. Both look blank in some tools, but only one has a missing result. The NULL indicator exposes that difference.

I wouldn’t replace every missing result with empty text automatically. That would hide the negative request along with genuinely missing inputs. A caller can reject negative counts before formatting. Keep that rejection policy separate from a display default.

Cases five and six supply a missing count and missing source respectively. Their expected repeated values remain NULL. These are additional input conditions, not evidence that the source AB was repeated successfully. Retain both columns during diagnosis.

Spaces remain repeated content

Case eight repeats one ordinary space twice. Its expected output contains two bytes of varchar text. The NULL flag remains zero. A blank-looking string can therefore contain stored content rather than zero characters.

DATALENGTH makes that distinction visible beside the output. It measures bytes rather than a linguistic character count. This example uses ordinary single-byte characters only. An nvarchar adaptation needs its own byte expectations.

Don’t trim the source during the comparison unless trimming belongs to the requirement. Removing the space would change this case’s meaning. Repetition and cleanup are different operations. I keep the supplied text intact while checking the repetition rule.

Bound the requested output

These eight inputs keep the largest output at six bytes. Larger repetition requests need an explicit capacity review. Without a max type, the output stops at 8,000 bytes. Binary input is also converted to varchar.

The example doesn’t use replication as a binary-copy operation. It also makes no claim about large-output performance. The CTE and SELECT create no objects or connection settings. Every requested count remains fixed in the copyable SQL.

Compare the result bytes and NULL flag together for each row. Retain zero, negative, missing and empty-source requests. Those inputs expose different outcomes behind similar displays. A row count alone wouldn’t check the repetition contract.

Count the copies you asked for, and a blank result explains itself.

An empty repeated string is not a missing result, it is what zero copies returns.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.