SQL Server concat

Question

I have 3 variables:

declare @A NVARCHAR(MAX), @B NVARCHAR(MAX), @C NVARCHAR(MAX)

@A is filled with 40000 characters, and @B is filled with 50000.

Now I want to concat @a and @b into @c like this:

@c = @a + @b

And update a column with @c. But when I concat them I lose some of the data.

How can I fix this?

Answer 1

The problem doesn't seem to be with the concatination itself.

You can test it with this SQL snippet:

declare @A NVARCHAR(MAX), @B NVARCHAR(MAX), @C NVARCHAR(MAX);
set @A = replicate(N'A',4000);
set @A = @A + @A + @A + @A + @A;
set @A = @A + @A;
set @B = replicate(N'B',2500);
set @B = @B + @B + @B + @B + @B;
set @B = @B + @B;
set @B = @B + @B;

-- CONCAT(@A,@B) gives the same result as @A+@B. 
-- But @A+@B would return NULL if @A or @B is NULL.
set @C = CONCAT(@A, @B);

select len(@A) as sizeA, len(@B) as sizeB, len(@C) as sizeC;

Returns :

sizeA   sizeB   sizeC
-----   -----   -----
40000   50000   90000

And a NVARCHAR(MAX) can contain up to 2^30-1 characters.
So 1073741823 characters is ... a lot. Reference here
And even if that's limited by a maximum storage size of 2 Gigabytes. Then it can still contain 1000000000 of 2-byte unicode characters.
In other words, about 3333.3 books that have an average of 300000 characters per book.
Many libraries could probably store all their novels in one NVARCHAR(MAX).

The problem could be caused by how @A and/or @B are filled up.

For example if you'd use REPLICATE.
It can only fill the NVARCHAR with up to 4000 characters.

Example:

declare @X NVARCHAR(MAX);
set @X = replicate(N'X',10000);
select len(@X) as sizeX;

Returns:

sizeX
-----
4000

So one question to ask is:

How were @A and/or @B filled up, and is the length of their data as expected?

And if @A or @B do contain the expected amount of characters?
But copy & paste of a cell to a text editor didn't get all characters?

It turns out that only up to 43679 characters can be copied into the clipboard from a cell with Non-XML data.
And there's also that limit for the Non-Xml data in the OPTIONS of SSMS.
See screenshot below.
So trying copy&paste 90000 characters would be truncated anyway.

One of the work-arounds could be to cast the NVARCHAR(MAX) to an XML type in the select. Since XML has that default 2MB limit.

Example snippet:

declare @X NVARCHAR(MAX);
set @X = replicate(N'0123456789',333); -- 3330
set @X = @X + @X + @X + @X + @X; -- 16650
set @X = @X + @X + @X + @X; -- 66600

select len(@X) as sizeX, cast(N'<X><![CDATA['+ @X +N']]></X>' as XML) as XasXml;

Answer 2

Can't insert more than 43679 character to sql server single cell?

its was useful

Figure out my data saved complety but SSMS - Can not paste more than 43679 characters from a column in Grid Mode