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

Question

A user of one of our databases is trying to submit an UPDATE query to a SQL Server 2005 database and the text is being truncated unexpectedly.

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

The query is roughly:

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 found after some experimentation is that when I remove the string concatenation, the query works as expected and the field is not truncated.

I was able to work around the limitation and keep the concatenation by wrapping each separate string in a CAST to VARCHAR(MAX), so there is an option if the user feels the use of char() is required.

What I think is going on is, whenever the concatenation operator is used, there's an implicit conversion happening to VARCHAR and that implicit conversion seems to be limited to VARCHAR(8000) instead of VARCHAR(MAX). So, before the string is even sent to the CONVERT function, it's already being truncated down to 8000 characters.

If I'm correct, is there a way to change that behavior?

If there is no way to change the behavior, is there another way to deal with the issue besides CAST?

Solution 1

You need to CAST the first string first.

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

Before, the inner text never goes more then 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 walkthrough of "why", please see my answer here "For Nvarchar(Max) I am only getting 4000 characters in TSQL?"

Solution 2

My experience is 1) that you only need to cast the leftmost item to 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 supply the whole text as one string literal, don't be confused by limitations for query results 8192 character per column or PRINT statement 8000 characters. You don't need to escape the '/' chars as char(...). The only thing you have to do is duplicate embedded apostrophes.

Solution 3

Solution

Create multiple SET Statements that add smaller than 8000 bytes each. SQL will automatically expand the string size during the concatenation.

Example

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

Background

I had this same problem, only none of the above worked. In my case, the code was using nvarchar(max). I tried converting the first string explicitly to nvarchar(max) but it didn't help. In my case, there were many lines being appended and so it was very easy to break them apart. Be aware that nchar takes of 2x char. In our case, the developer changed from char to nchar which is what caused our bug and pushed us over the 8000 byte edge. We were just over 4000 characters (I'm not saying this is a good practice :) and the switch to nchar doubles it and then we go over the 8000 char limit.

Why

Why does this work? I'm sure some SQL head could tell us, but I believe it is related to how SQL will dynamically allocate space for nvarchar(max) based on the need. For some reason, it is not able to do that within a single SET statement. But if you break them up, with each new concatenation it does reevaluate and allocate the needed space when it detects you go over the 8000 limit.