Tuesday, February 03, 2009

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