Using REPLICATE to Build Long Strings

REPLICATE is a very useful tool, but there are limitations to its "out of the box" functionality.

Problem
This code shows the problem:

SELECT LEN(REPLICATE('X',  1000))
SELECT LEN(REPLICATE('X',  5000))
SELECT LEN(REPLICATE('X', 10000))

The result is:

----
1000

----
5000

----
8000

REPLICATE will not, by default, create a string longer than 8,000 characters. Here is the test that shows that the longest string is exactly 8,000 characters:

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE('X', @ct)

  IF len(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', len(@s1) AS 'Len @s1'
    BREAK
  END
END

---

@ct   Len @s1
----  -------
8001     8000

Fix
How can you make it build a longer string? Use CAST.

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE(CAST('X' AS varchar(max)), @ct)

  IF LEN(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', LEN(@s1) AS 'LEN @s1'
    BREAK
  END
END
SELECT LEN(@s1) AS 'Final String Length'

---

Final String Length
-------------------
              10000