Using VARCHAR (MAX) with string concatenation in SQL Server 2005

Question

A user of one of our databases is trying to send an UPDATE query to the SQL Server 2005 database, and the text will be truncated unexpectedly.

The truncated field is VARBINARY (MAX) and is used to store HTML text.

Request approximately:

UPDATE Story SET mainText = CONVERT (VARBINARY (MAX), '[...5000 chars of text...]' + char(47) + char(47) + '[...3000 chars of text...]' + char(47) + char(47) + '[...5000 chars of text...]') WHERE storyId = 123456 

What I discovered after some experimentation is that when deleting the string concatenation, the query works as expected, and the field is not truncated.

I managed to get around the restriction and save the concatenation by wrapping each individual line in CAST to VARCHAR (MAX), so there is an option if the user considers that char () is to be used.

I think that whenever an concatenation operator is used, an implicit conversion occurs with VARCHAR, and that the implicit conversion is apparently limited to VARCHAR (8000) instead of VARCHAR (MAX). Thus, before the string is even sent to the CONVERT function, it is already truncated to 8000 characters.

If I am right, is there a way to change this behavior?

If there is no way to change the behavior, is there any other way to handle the problem besides CAST?

Answer 1

You need to press the first line first.

 CONVERT (VARBINARY (MAX), '[...5000 chars of text...]') + char(47) + char(47) + '[...3000 chars of text...]' + char(47) + char(47) + '[...5000 chars of text...]' 

Prior to this, the internal text never exceeds 8000 bytes. Then you are casting. Too late.

 '[...5000 chars of text...]') + char(47) + char(47) + '[...3000 chars of text...]' + char(47) + char(47) + '[...5000 chars of text...]' 

For a more complete overview of “why,” please see my answer here “For Nvarchar (Max), do I only get 4000 characters in TSQL?”

Answer 2

My experience is 1) that you only need to drop the leftmost element in varchar (max):

 UPDATE Story SET mainText = CONVERT (VARBINARY (MAX), '[...5000 chars of text...]') + char(47) + char(47) + '[...3000 chars of text...]' + char(47) + char(47) + '[...5000 chars of text...]' WHERE storyId = 123456 

2) You can provide all the text as a single string literal, do not confuse the limits for the query results of 8192 characters per column or PRINT expression of 8000 characters. You do not need to avoid the "/" characters like char (...). The only thing you need to do is duplicate the built-in apostrophes.

Answer 3

You should try to do CONVERT for every row you concatenate. Or you can split your query into more than one UPDATE (assuming the body of the column is VARBINARY(MAX) ).

 UPDATE Story SET mainText = CONVERT (VARBINARY (MAX), '[...5000 chars of text...]' ) + char(47) + char(47) + CONVERT (VARBINARY (MAX),'[...3000 chars of text...]') + char(47) + char(47) + CONVERT (VARBINARY (MAX),'[...5000 chars of text...]') WHERE storyId = 123456 

or

 UPDATE Story SET mainText = '[...5000 chars of text...]' WHERE storyId = 123456 UPDATE Story SET mainText = maintext + char(47) + char(47) + '[...3000 chars of text...]' WHERE storyId = 123456 UPDATE Story SET mainText = maintext + char(47) + char(47) + '[...5000 chars of text...]' WHERE storyId = 123456 

Answer 4

Your truncation happens because you first concatenate the strings [...5000 chars of text...]' + char(47) + char(47) + '[...3000 chars of text...]' + char(47) + char(47) + '[...5000 chars of text...]') , and then convert to VARBINARY (MAX).

You must first convert each row to VARBINARY (MAX) , and then concatenate

 UPDATE Story SET mainText = CONVERT (VARBINARY (MAX), '[...5000 chars of text...]' ) + CONVERT (VARBINARY (MAX), char(47) + char(47)) + CONVERT (VARBINARY (MAX), '[...3000 chars of text...]') + CONVERT (VARBINARY (MAX), char(47) + char(47) + CONVERT (VARBINARY (MAX), '[...5000 chars of text...]') ) WHERE storyId = 123456 

Answer 5

Decision

Create multiple SET records that add less than 8000 bytes each. SQL will automatically expand the row size during concatenation.

Example

 declare @SQLStatement nvarchar(Max) SET @SQLStatement = '[...first block of text...]' SET @SQLStatement = @SQLStatement + '[...next block of text...]' ... 

Background

I had the same problem, but there was none of this. In my case, nvarchar (max) was used in the code. I tried converting the first line explicitly to nvarchar (max), but that didn't help. In my case, many lines were added, so it was very easy to split them. Keep in mind that nchar takes up 2x char. In our case, the developer changed from char to nchar, which caused our error and pushed us to the edge of 8000 bytes. We were just over 4,000 characters (I'm not saying this is good practice :), and switching to nchar doubles it, and then we overcome the 8000 char limit.

Why

Why does it work? I'm sure some SQL manager can tell us, but I believe that this is due to how SQL will dynamically allocate space for nvarchar (max) based on need. For some reason, it cannot do this within a single SET statement. But if you decompose them, with each new concatenation, he reevaluates and allocates the necessary space when he discovers that you are breaking the 8000 limit.