Is there any limitation of the way we perform concatenation in SQL server?

Question

Post reply

Is there any limitation of the way we perform concatenation in SQL server?

ravi090191

SSC Enthusiast

Points: 162
More actions
November 27, 2013 at 4:23 am

#301705

hi,
It may sounds lame but can anybody help me in understanding this issue?
please help.....
This query does not work:
SET @tSprocSQL1 = @tMidSQL1+@tMidSQL2+@tMidSQL3+@tMidSQL4+@tMidSQL5+@tMidSQL6+@tMidSQL7+@tMidSQL8+@tMidSQL9+@tMidSQL10+@tEndSproc+ CHAR(13);
Whereas this query works:
SET @tSprocSQL1 = @tMidSQL1+@tMidSQL2+@tMidSQL3+@tMidSQL4+@tMidSQL5+@tMidSQL6+@tMidSQL7+@tMidSQL8
SET @tSprocSQL1 = @tSprocSQL1+@tMidSQL9+@tMidSQL10+@tEndSproc+ CHAR(13);
___________________________________________________________
@tSprocSQL1 - is nvarchar(max)
while all tMidSQL are navarchar(4000)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

What do you mean with "This query does not work"?

Do you get an error message?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ravi090191 SSC Enthusiast Points: 162 More actions · Answer 2

It seems the problematic query truncates data above 4000 bytes.

it does not giving error, but truncating the data.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

What is the data type of @tSprocSQL1?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ravi090191 SSC Enthusiast Points: 162 More actions · Answer 4

@tSprocSQL1 is initialized as a blank string.

DECLARE @tSprocSQL1 nvarchar(max)

SET @tSprocSQL1 = N' '

ravi090191 SSC Enthusiast Points: 162 More actions · Answer 5

while searching this issue over the internet I have come across an assertion as, On concatenating nvarchar + navarchar, it will truncate at 4000 characters.

I have check this limitation on SQL server 2k8 and 2k12.

But I don't get as why do we have such limitation?

ravi090191 SSC Enthusiast Points: 162 More actions · Answer 6

ravi090191

SSC Enthusiast

Points: 162

November 27, 2013 at 6:47 am

#1669460

will see your response 2moro... off to home..

Lowell SSC Guru Points: 323518 More actions · Answer 7

you have to make sure ALL of your variables are the same (nvarchar(max))

Truncation when concatenating depends on datatype.

if you do something like this:

nvarchar(max) + varchar(n)

SQL will first convert the varchar(n) input to nvarchar(n) and then do the concatenation. If the length of the varchar(n) string is greater than 4,000 characters the cast will be to nvarchar(4000) and truncation will occur.

this has a nice post that summarizes some of the truncation gotchas:

http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!