Wednesday, 18 September 2013

String or Binary Data Would Be Truncated on varchar(max)

String or Binary Data Would Be Truncated on varchar(max)

I've checked around and everyone seems to be running into issues with
column lengths being too small. I don't think that's my issue as
everything is a varchar max.
I have a large text string being stored in a column
Select len(templateDocument) from TemplateDocument
This Returns 5142610
I'm trying to do a replace of old text with new text and running into this
issues
UPDATE TemplateDocument
SET templateDocument = REPLACE(templateDocument, @BookmarkOldText,
@BookmarkNewText)
This is where my error get's thrown.
My NewText is of length 12067, the old text is of 14279. Seeing as my new
text is less than my new text, it should fit and shouldn't be throwing an
error. I can even double the current text in a varchar(max) and that fits
fine.
declare @fullItem varchar(max)
SELECT @fullItem = templateDocument from TemplateDocument
SET @fullItem = @fullItem + ' ' + @fullItem
Select LEN(@fullItem)
This Returns 10285221
Which is fine and it still fits into the datatype and then into the column.
So my question is why on the replace is it saying there's going to be
truncation? It looks as if the datatype is large enough to store the whole
document twice over. Is there a size limit on the replace function? If so,
is there a better way of going through and replacing this?

No comments:

Post a Comment