Geek Answers Handbook

Nvarchar (Max) SQL Server 2008 Concatenation - Truncation Problem

Can someone explain why this is happening on SQL Server 2008:

declare @sql Nvarchar(max);

set @sql =N'';

select @sql = @sql +replicate('a',4000) + replicate('b', 6000);

select len(@sql)

Returns: 8000

Several sites suggest that as long as the first variable is of type NVARCHAR (MAX), truncation should not occur, but it does anyway.

+4
source share
2 answers

Because 'a'and 'b'do not relate to the type ofNVARCHAR(MAX)

Like this, it should work:

declare @sql Nvarchar(max),
   @a nvarchar(max),
   @b nvarchar(max);

select @sql =N'', @a = N'a', @b = N'b';

select @sql = @sql +replicate(@a,4000) + replicate(@b, 6000);

select len(@sql)

This is a link to Microsoft REPLICATE function information: https://msdn.microsoft.com/en-us/library/ms174383.aspx

It says:

string_expression varchar (max) nvarchar (max), REPLICATE 8000 . 8000 , string_expression .

+4

, , . , NVARCHAR 8000, NVARCHAR (MAX), CONCAT():

DECLARE @sql Nvarchar(max);

SELECT @sql = CONCAT(@sql,replicate('a',4000),replicate('b', 6000)) --do it this way

--SELECT @sql = CAST(replicate('a',4000) AS NVARCHAR(MAX)) + CAST(replicate('b', 6000) AS NVARCHAR(MAX)) --or this way

LEN (@sql) 10 000

+1

More articles:


All Articles