1

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?

Jabsy
  • 171
  • 4
  • 16

1 Answers1

-3

The problem may be VARCHAR(MAX) which has capacity to store 8000 length

for more details please have a look at

http://msdn.microsoft.com/en-us/library/ms176089.aspx

Pawan
  • 1,065
  • 5
  • 10
  • I've looked at this multiple times and the input and output all seem to fit. If the varchar(max) can only hold 8000, how can it hold the combined size of the original document? – Jabsy Sep 18 '13 at 09:22
  • 1
    No, varchar(MAX) can store up to 2GB, read the documentation carefully. – Lukas.Navratil Aug 06 '14 at 11:01
  • To quote the link you shared: "varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000 or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB)." This shows that your answer is incorrect. The storage size limit is 2,147,483,647 bytes, not 8000. – Grungondola Oct 21 '19 at 12:27