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?

• Lowell

  SSC Guru

  Points: 323482

  More actions

  August 24, 2015 at 9:57 am

  #1822021

  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: 183686

  More actions

  August 24, 2015 at 10:02 am

  #1822023

  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: 100262

  More actions

  August 24, 2015 at 10:39 am

  #1822034

  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('x', 2000)+ char(10) +

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

  'abc'

  select len(@t)

  SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• seaport

  SSCrazy

  Points: 2491

  More actions

  August 24, 2015 at 11:45 am

  #1822062

  Thank you all for the quick responses.