Interesting results with Len and varchar(max)

Question

Interesting results with Len and varchar(max)

seaport

SSCrazy

Points: 2491
More actions
August 24, 2015 at 9:49 am

#302269

I am getting the following results from SQL2008R2 through SSMS.
-- test 1
declare @t as varchar(max)
select @t='abc' + replicate('x', 2000) + char(10) +
'abc' + replicate('x', 2000)+ char(10) +
'abc' + replicate('x', 2000)+ char(10) +
'abc'
select len(@t)
-- return 6015
-- test 2
declare @t as varchar(max)
select @t='abc' + replicate('x', 2000) + char(10) +
'abc' + replicate('x', 2000)+ char(10) +
'abc' + replicate('x', 2000)+ char(10) +
'abc' + replicate(' ', 2000)+ char(10) +
'abc'
select len(@t)
-- return 6015
-- test 3
declare @t as varchar(max)
select @t='abc' + replicate('x', 2000) + char(10) +
'abc' + replicate('x', 2000)+ char(10) +
'abc' + replicate('x', 2000)+ char(10) +
'abc' + replicate('x', 2000)+ char(10) +
'abc'
select len(@t)
-- return 8000
Can anyone help me explain the results from test 2 and test 3?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

https://msdn.microsoft.com/en-us/library/ms174383.aspx

Note
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

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!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

The problem is that your concatenation creates a varchar(8000) string which is then converted to be stored in a varchar(max).

In both test 2 and 3, it gets truncated in the last set of the characters created by the replicate function. On test 2, the trailing spaces are trimmed, while in test 3 it's just truncated in the 8000 characters limit.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101093 More actions · Answer 3

Iow, be sure to cast at least one of the values in the expression to (max):

select @t=cast('abc' as varchar(max)) + replicate('x', 2000) + char(10) +

'abc' + replicate('x', 2000)+ char(10) +

'abc' + replicate(' ', 2000)+ char(10) +

'abc'

select len(@t)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

seaport SSCrazy Points: 2491 More actions · Answer 4

seaport

SSCrazy

Points: 2491

August 24, 2015 at 11:45 am

#1822062

Thank you all for the quick responses.