This in-fact happens to almost every string function. See how the concatenation goes wrong.

DECLARE @s1 VARCHAR(5000) = REPLICATE('a', 5000)
DECLARE @s2 VARCHAR(5000)= REPLICATE ('a', 5000)

SELECT LEN(@s1 + @s2) AS length
/*
length
-----------
8000 <== WRONG! WRONG!
*/

SELECT LEN( CAST(@s1 AS VARCHAR(MAX)) + @s2) AS Length
/*
Length
--------------------
10000
*/

Edit: Based on Martin's comments

yessss..........Its the case with other string functions also.....

I had a blogpost about it here http://beyondrelational.com/blogs/madhivanan/archive/2008/07/15/varchar-max-datatype-and-replicate-function.aspx

I wrote something similar a few years ago and it is posted at http://beyondrelational.com/blogs/jacob/archive/2008/01/12/varchar-nvarchar-n-vs-max.aspx

@Jacob - Your example with LEN is not a good one.

The issue there is that the result of the concatenation (@s1 + @s2) doesn't get automatically cast to varchar(max). Nothing to do with the LEN function.

@Martin - You are right; it is not the best example we could show :-). However, the intention was to demonstrate the same behavior you pointed out and I thought it was apparent from what I posted.

I edited the original post.

Probably to make it clear, Its not the len() function that caused the problem. Its operator precedence in "concatenation" operation that caused the problem. In Jacob's example. the datatypes are varchar(5000). So, while concatenating it estimated that result will be varchar(8000). So, it truncated remaining text.

DECLARE @s1 VARCHAR(max) = REPLICATE('a', 8000)
DECLARE @s2 VARCHAR(max)= REPLICATE ('a', 8000)
DECLARE @s3 VARCHAR(max)= REPLICATE ('a', 2000)

select LEN(@s1+@s2+@s3)

Above example works fine. Here the datatypes are varchar(max). So, the result also it estimates that it will be varchar(max) and will correctly do the work.

I saw recently a similar question asked - wondering if it's somehow related? http://tek-tips.com/viewthread.cfm?qid=1650790&page=1

??

Never tried it (or encountered a similar situation), but now that I think about it, it is perfectly logical that every string function will demonstrate a similar behaviour.

Can any one here point out a business use-case where you would want to replicate more than 8000 instances of the same character?