The other day I was working on a script to update a value inside of a long string that is stored in a database. While doing this my script threw some errors. Specifically the Replace function error was showing “String or Binary Data will be truncated” while using NVARCHAR(Max). But, I was passing in variables with a data type NVARCHAR(MAX) and just doing a select to test my query, where is the truncation happening here? I was a bit confused, so naturally I started searching for answers.
Why is my replace function getting an error?
It turns out the Replace function has a limitation on the output the Microsoft BOL says which I thought was where my problem was.
If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.
But this actually was not my issue, that is because the truncation is occurring with the text to be matched (2nd parameter). There is a size limitation on string pattern to match. I was trying to replace a pretty large piece of text.
According to BOL for the string_pattern parameter:
string_pattern
Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern must not exceed the maximum number of bytes that fits on a page.
The only way I was able to find a way around this is to declare it as varchar(max) which would be 8,000 bytes. Unfortunately this means if your string pattern is Unicode and you need to use NVARCHAR you are kind of out of luck.
Here is a sample of the issue:
DECLARE @VarcharString VARCHAR(MAX),
@match VARCHAR(MAX),
@replace VARCHAR(MAX)
DECLARE
@NvacrCharString NVARCHAR(MAX),
@Nmatch NVARCHAR(MAX),
@Nreplace NVARCHAR(MAX)
SELECT @VarcharString = REPLICATE('a',6000),
@NvacrCharString = REPLICATE('a',6000),
@match = REPLICATE('a',6000),
@replace = REPLICATE('b',6000),
@Nmatch = REPLICATE('a',6000),
@Nreplace = REPLICATE('b',6000)
SELECT DATALENGTH(@VarcharString), DATALENGTH(@NvacrCharString)
SELECT REPLACE(@VarcharString,@match,@replace) -- Works
SELECT REPLACE(@NvacrCharString,@Nmatch,@Nreplace) -- Fails
It looks like the replace function is really meant to be used to change small chunks of text, like replace a date somewhere or a comma for a space etc… If you need to replace large pieces of text we should probably do it using some other language not in TSQL. I wonder if PowerShell would do better… If you have a different way around this, please comment your solution; I’d be very interested to hear how you worked around it.
