Monthly Archives: November 2013

REPLICATE

While creating some CLR user defined functions I discovered that using data passed into my nvarchar(max) parameter was getting truncated if it was more than 8000 bytes.

For about a day and a half I suspected that the C# code was not set up to properly handle LOBs and that it was getting truncated. I tried every solution I found online and nothing helped.
pullhairout

I was creating the LOBs using the REPLICATE function. My last resort was to make sure I really was passing in more than 8000 bytes which lead me to read the documentation for REPLICATE.

It says:

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.

There was my problem.

Test code:

DECLARE @string nvarchar(max)
DECLARE @result nvarchar(max)

SET @string = N'A'

SET @result = REPLICATE(N'A',4001)
SELECT LEN(@result)

SET @result = REPLICATE(CAST(N'A' as nvarchar(max)),4001)
SELECT LEN(@result)

SET @result = REPLICATE(@string,4001)
SELECT LEN(@result)

Results:
ReplicateLOBResults

This is the link to the request for assistance on SQL Server Central if you are interested in the details of the LOB parameters to CLR functions.

Hair Pulling Photo Attribution: By stewartpilbrow under CC BY-SA 2.0