SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters

It has been a long time since we have played a puzzle over this blog. This Monday, let us play a quick puzzle.

SQL Server have REPLICATE function which will replicate the string passed as many as times as the second parameter. For example execute following string.

SELECT 'Ha'+REPLICATE('ha',20)

The script above will return following result:

`

You can notice that it has returned a string ha about 20 times after first Ha.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Here is the second part of the Quiz.

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

Please leave a comment with your answer. I will publish the answer to this puzzle on coming Friday’s blog post and will mention the names of the all the valid users.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

155 thoughts on “SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters

  1. The string_expression component of the REPLICATE function needs to be VARCHAR(MAX) or NVARCHAR(MAX) to enable the output string to exceed 8000 characters

    So for the last example use:

    DECLARE @ThirdString VARCHAR(MAX);
    DECLARE @ThirdSource VARCHAR(MAX);
    SET @ThirdSource=’C';
    SET @ThirdString = REPLICATE(@ThirdSource,11000);
    SELECT LEN(@ThirdString) LenThirdString;

    or

    DECLARE @ThirdString VARCHAR(MAX);
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000);
    SELECT LEN(@ThirdString) LenThirdString;

  2. Answer
    Quiz 1 : Last result is 8000, because maximum length of varchar is 8000 (if varchar(max) is not specified). We passed only varchar value to replicate function,so it returns value as varchar(8000)

    Quiz 2: To get 11000 result, replace second last line by following line
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

  3. Hi Pinal,
    I think there is a typo in Quiz 1, the length of the @ThirdString is 8000 but not 4000. I think it is because the size if varchar(max) is 8000 characters, so when we use it implicitly then it restricted to 8000 characters.
    To get the length greater than 8000 characters I used this statement:

    SET @ThirdString = REPLICATE(CAST(‘C’ AS varchar(max)),11000);

    Best regards,
    Arsen

  4. you need to force the data type to varchar(MAX) like so

    DECLARE @ThirdString VARCHAR(MAX)
    SET @ThirdString = REPLICATE(CAST(‘D’AS varchar(MAX)),11000)
    SELECT LEN(@ThirdString ) LenThirdString;

  5. Hi Pinal,

    Answer1 : — In this case SQL server is displaying varchar maximum length which is 8000.

    Answer2 : – To resolve this problem we need to use cast function and statement should be —
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

  6. Hi Pinal,

    Answer1 : — In this case SQL server is displaying varchar maximum length which is 8000.

    Answer2 : – To resolve this problem we need to use cast function and statement should be —
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

  7. DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),’C’),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  8. I think 8000 character is the initial max limit for Replicate..to go beyond that we need explicit conversion..I hv just tried it and it worked for me..But please make me clear sir..
    Thanx

  9. HI,

    Answer 1 :-
    The REPLICATE function frequently uses long strings, and therefore is more likely to incur the 4000-character limit on expression length.

    Answer 2 :-
    REPLICATE function truncate after 8000 character. I came across this problem few months ago, when i was playing with varchar. If we want to show Correct character len, then we have to break it in two parts and explicitly convert it again in Varchar(max). Have a look on below code.

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;

    —- INCORRET
    SET @ThirdString = REPLICATE(‘C’,11000)
    SELECT LEN(@ThirdString) LenThirdString;

    —- CORRECT
    SET @ThirdString = CAST(REPLICATE(‘C’,5000) as VARCHAR(max))+REPLICATE(‘C’,6000)
    SELECT LEN(@ThirdString) LenThirdString;

    Thanks
    Vinay Kumar

  10. Hi Pinal,

    First of all, I am a great fan of your articles and posts… You have really helped me a lot in gaining speed in SQL.

    Now the answer:

    First the Correct SQL to display as per requirement:

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;

    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

    SELECT LEN(@ThirdString) LenThirdString;

    Reason:

    Replicate has two parameters: The first one in the character expression and second one is the integer value…

    The second one is not of the concern here.

    But the Character expression is implicitly convertible to NVARCHAR or NTEXT and by default as we all know it defaults to 8000 characters. So, the output of the Replicate function can never be more than 8000 characters

    So in order to get something more than 8000, we will need to CAST the character expression into MAX (VARCHAR/NVARCHAR) and then perform the replicate on the Character. Now since the variable getting assigned to is also specified in MAX, so it will store 11000 characters and LEN will display the correct value.

  11. DECLARE @String1 AS VARCHAR(3000)
    DECLARE @String2 AS VARCHAR(6000)
    SET @String1=REPLICATE(‘a’,3000)
    SET @String2=REPLICATE(‘b’,6000)
    SELECT LEN(@String1+@String2) AS Result_String

    –RESULT IS 8000

    –Now if we change datatype to VARCHAR(MAX)

    DECLARE @String1 AS VARCHAR(MAX)
    DECLARE @String2 AS VARCHAR(MAX)
    SET @String1=REPLICATE(‘a’,3000)
    SET @String2=REPLICATE(‘b’,6000)
    SELECT LEN(@String1+@String2) AS Result_String

    –RESULT IS 9000

    –I think whats happening in above query is Result_String is varchar(9000) which exceeds the limit which is VARCHAR(8000)..so to get correct results we need to cast or convert it to varchar(max)

    –in second query strings are of VARCHAR(MAX) type so the result will be of varchar(MAX) type and we get the right result..

  12. hello dave
    this script will replicate string over 8000 characters
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  13. Pinal,

    My answers are as follows :

    Ans 1 :

    REPLICATE function will truncate the value at 8000 bytes. We need to explicitly convert to VARCHAR(MAX) for large string (Ref Ans 2).

    Ans 2 :

    SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),’C’),11000)
    SELECT LEN(@ThirdString) LenThirdString;

    Note : Please change @ThirdString value 8000 instead of 4000 in Quiz 1.

  14. 1) REPLICATE support 8000 at a time
    2) 11000 len can be achieve by following way
    SET @ThirdString = REPLICATE(‘C’,8000)
    SET @ThirdString = @ThirdString + REPLICATE(‘C’,3000)
    SELECT LEN(@ThirdString) LenThirdString;

  15. Quiz 1: REPLICATE function truncates the return value at 8,000 bytes.
    Quiz 2: This is the full answer.
    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    DECLARE @FourthString VARCHAR(MAX)
    DECLARE @FifthString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(‘C’,8000)
    SELECT LEN(@ThirdString) LenThirdString;
    SET @FourthString = REPLICATE(‘C’,3000)
    SELECT LEN(@FourthString) LenFourthString;
    SET @FifthString = @ThirdString+@FourthString;
    SELECT LEN(@FifthString) LenFifthString;

  16. DECLARE @ThirdString VARCHAR(MAX)

    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000);

    SELECT LEN(@ThirdString)

    varchar [ ( n | max ) ] Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

  17. you have to cast replicate character to varchar(max)…

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(max)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(CAST(‘C’ AS varchar(MAX)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  18. 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.
    So we need use cast to varchar(max)
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(max)),11000)

  19. Quiz 1: LEN(@ThirdString) is 8000 not 4000. Anyway, as a varchar can’t be more than 8000 characters if it isn’t declares as VARCHAR(MAX) the Replicate-function truncates the return value at 8000 bytes if the string to be repeated isn’t of the type VARCHAR(MAX) or NVARCHAR(MAX). Which it (‘C’) isn’t in the case.

    Quiz 2: One would have to cast the string to be replicated to VARCHAR(MAX) or NVARCHAR(MAX), like this:
    SET @ThirdString = REPLICATE(CAST(‘C’ as varchar(max)),11000)
    Then the length of @ThirdString will be 11000

  20. 1. Because the data type of input is not varchar(max).

    2. Explicitly make the input as varchar(max) like the following:
    DECLARE @ThirdString VARCHAR(MAX)
    DECLARE @a VARCHAR(MAX) = ‘C’
    SET @ThirdString = REPLICATE(@a,11000)
    SELECT LEN(@ThirdString) LenThirdString;

  21. The replicate functions return value is the same type as the first arguments type (character or binary by default) thus it truncates at 8000 bytes (max length of these two types).
    You need to cast the first argument to varchar(max) or nvarchar(max) to get the appropriate result.

    Also. in quiz 1 you have a typo: @ThirdStrings length is 8000 not 4000.

  22. Declare ‘C’ as a VARCHAR(MAX) variable

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    DECLARE @C VARCHAR(MAX) = ‘C’

    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(@C,11000)
    SELECT LEN(@ThirdString) LenThirdString;

  23. Would like to change my answer….

    1. The length came as 8000 because Replicate function returns an nvarchar?
    2. This gives us 11000 length for the third replicate
    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),’C’),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  24. Answer for Quiz 1 : The length of the @ThirdString is shown 8000 because varchar(max) can take maximum 8000 characters.

  25. SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

    SELECT LEN(@thirdString) LenThirdString

    This should do the trick.

    Regards,
    Sreekanth

  26. REPLICATE truncates the return value at 8,000 bytes therefore max values as length shown in result here is 8000.

  27. According to me,We shall make the below changes to get the desired output.
    SET @ThirdString = REPLICATE(convert(varchar(max),’C’),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  28. Hi,
    To get 11000 length we need to use following query by using Replicate.

    SET @ThirdString = REPLICATE(‘C’,1000)
    SELECT @ThirdString = REPLICATE(@ThirdString,11)
    SELECT LEN(@ThirdString) LenThirdString;

    Thank you.

  29. Hi Pinal Dave,
    I hope this is correct.

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;

    SET @SecondString = REPLICATE(‘B’,8000)

    SELECT LEN(@SecondString) LenSecondString;

    SET @ThirdString = REPLICATE(‘C’,8000)
    SET @ThirdString = @ThirdString + REPLICATE(‘C’,3000)

    SELECT LEN(@ThirdString) LenThirdString;

  30. Hi Pinal,
    Is This Correct :

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(‘C’,11000)
    SELECT LEN(@ThirdString)+3000 LenThirdString;

  31. You have to cast the ‘C’ as a varchar(max) as well, or else it treats the whole string as a varchar(8000).

  32. When ‘C’ is introduced inside REPLICATE without previous declaration it is implicitly declared as VARCHAR(1). When that ‘C’ being VARCHAR(1) is concatenated to another ‘C’ which is also VARCHAR(1), the result will be ‘CC’ – VARCHAR(2). Adding another ‘C’ to it will make ‘CCC’ VARCHAR(3) and so on. That is basically what REPLICATE will do until reaching VARCHAR(8000). Adding another VARCHAR(1) to VARCHAR(8000) will not increase VARCHAR to MAX or over its 8000 limit so the all further additions will be lost and the result remains VARCHAR(8000).

    Storing that result back to declared VARCHAR(MAX) variable will convert it to MAX, and additional characters can be added, like this:

    DECLARE @FourthString VARCHAR(MAX)
    SET @FourthString = REPLICATE(‘C’,8000)
    SET @FourthString = @FourthString + REPLICATE(‘C’,3000)
    SELECT LEN(@FourthString) LenFourthString;

    Or – even better way – declaring ‘C’ as VARCHAR(MAX) right in the beginning will make it possible to replicate it more than 8000 times.

    DECLARE @FifthString VARCHAR(MAX)
    SET @FifthString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
    SELECT LEN(@FifthString) LenFifthString;

    Best Regards
    -Nenad Zivkovic

  33. DECLARE @ThirdString VARCHAR(MAX)
    SET @ThirdString = CONVERT(VARCHAR(MAX),REPLICATE(‘C’,8000)) + CONVERT(VARCHAR(MAX),REPLICATE(‘C’,3000))
    SELECT LEN(@ThirdString) LenThirdString;

  34. DECLARE @ThirdString VARCHAR(MAX)
    SET @ThirdString = REPLICATE(CAST(‘a’ AS varchar(max)),11000)
    SELECT LEN(@ThirdString) LenThirdString

    Would do the trick probably… as the replication will be treated as non-max datatype otherwise.

  35. SET @ThirdString = cast(REPLICATE(‘C’,8000) as varchar(max)) + cast(REPLICATE(‘C’,3000) as varchar(max))

  36. varchar(max) can only store 8000 char in it at a time. So as the type of srting is varchar(max) so it is only storing 8000 chars and skipping the rest.

  37. 1)
    In SQL Server,page size 8 kb. Replication function allocates 8kb for whatever character expression that we have to replicate(first argument).for undeclared length of character expression in replication function,it can be replicate through 8000 character not more than this means 8kb.
    2)solution is to give a max length/defined length for character expression for page allocation.
    Query would be :
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  38. 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.

  39. Thank you for answers my friends – we are officially over 50 valid answers – I will soon publish them, once everybody has fair chance to participate.

  40. declare @third varchar(max)
    select @third= REPLICATE(cast(‘c’ as varchar(max)),11000)
    select len(@third)

    this will solve the issue by explicitly casting string as varchar (max).

    my previuos assumption about varchar(max) storing 8000 char only was wrong.
    I am still confused about difference between varchar(8000) and varchar(max)

  41. Hello:

    REPLICATE is causing that behaviour. As the MSDN states: “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.”

    The solution would be to write the script for @ThirdString as follows:

    DECLARE @ThirdString VARCHAR(MAX)
    SET @ThirdString = REPLICATE(CAST(‘C’ AS nvarchar(max)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  42. DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE((CAST(‘C’ AS varchar(max))),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  43. DECLARE @ThirdString VARCHAR(MAX)
    SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

    The ‘C’ causes the REPLICATE function to return a value of data type varchar(8000). If more than 8000
    characters are required explicit conversion of the string to be replicated to varchar(MAX) is required.

  44. 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.

  45. That happens because SQL Server uses the data type of the first parameter as the data type of the result, which in this case will be VARCHAR(8000). So you need to cast the first parameter to VARCHAR(MAX) like as follows:
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

  46. Hi, so, the answer really is not to hard to find, you just need to visit msdn: http://msdn.microsoft.com/en-us//library/ms174383.aspx -read the most valuable thing there: the note, and then simply realize that when you write ‘A’ you actually use char(1) whitch has the mentioned limitation. So in order to make it work you’d have to cas/convert the function parameter :

    DECLARE @ThirdString VARCHAR(MAX)
    SET @ThirdString = REPLICATE(convert(varchar(max), ‘C’),11000)
    SELECT LEN(@ThirdString) LenThirdString;

    tested, works ;)

  47. In replicate if string expression is not of Varchar(Max) type then replicate truncate its length to 8000

  48. HI PINAL
    YOU CAN DO SOMETHING LIKE THIS TO FIX THE ISSUE:

    DECLARE @ThirdString varchar(max)
    SELECT @ThirdString = replicate( cast( ‘C’ AS varchar(max)), 20000 )
    SELECT datalength( @ThirdString )

  49. Answer for the 2nd question:
    One way to do it is

    SET @ThirdString = REPLICATE(‘C’,8000)
    SET @ThirdString = @ThirdString + REPLICATE(‘C’,3000)
    SELECT LEN(@ThirdString) LenThirdString;

    This will yield 11000 for the Len.

  50. Quiz 1 : The length is 8000 is because that what a varchar without max declaration can hold i.e REPLICATE(‘C’ ,11000) where c is considered as a varchar data type not as varchar(max) .

    Quiz 2: convert the datatype of ‘c’ to varchar(max) i.e REPLICATE(CAST(‘C’ as VARCHAR(MAX)),11000)

  51. 1: default type for explicit string constant is varchar which causes replicate function to limit the resultant string to 8000.

    2: Make the type explicit:
    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    DECLARE @SorceString varchar(MAX)
    SET @SorceString = ‘A’
    SET @FirstString = REPLICATE(@SorceString,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SorceString = ‘B’
    SET @SecondString = REPLICATE(@SorceString,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @SorceString = ‘C’
    SET @ThirdString = REPLICATE(@SorceString,11000)
    SELECT LEN(@ThirdString) LenThirdString;

  52. Quiz 1:

    Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 4000. WHY?

    SOULD
    Quiz 1:

    Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

  53. Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 4000. WHY?

    — I think you meant, “@ThirgString is only 8000″ based on the screen shot and second question. So I will answer with this in mind. If it isn’t, please let me know. But anyway, this is because REPLICATE truncates the return value at 8,000 bytes.

    What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

    –The string_expression must be explicitly cast to the appropriate large-value data type. In this case it could be VARCHAR(MAX). So it would need to be:

    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  54. REPLICATE will return data in the same format as what it receives right? So since we are passing ‘C’ we are passing a VARCHAR() not specifically a VARCHAR(MAX) so we end up being limited to 8000 characters. I used a CONVERT(VARCHAR(MAX)) to get around this issue.

    Nice puzzle, It took me a while to get it, but I did!

  55. Hi Pinal Dave. I love your site! This is an interesting puzzle. I believe that it is necessary to explicitly cast the string that is passed in to a large value data type like this:
    SET @ThirdString = REPLICATE(CAST(‘C’ as VARCHAR(MAX)),11000)

  56. DECLARE @FirstString VARCHAR(MAX)
    SET @FirstString = REPLICATE(Convert(Varchar(max),’A’),11000)
    SELECT LEN(@FirstString) LenFirstString;

  57. DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),’C’),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  58. It works like this because in Len funtion it won’t cast automatically.
    SELECT LEN( CAST(@Thirdstring AS VARCHAR(MAX)))

  59. Answer to Quiz 1 – If the first argument of Replicate function is not of Varchar(max) or nvarchar(max) type, then it will return 8000 bytes only.

    Answer to Quiz 2 – To return more than 8000 character from Replicate function, we must need to convert first argument (‘C’ in this case) explicitly to varchar(max). So the following change is required:

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString nvarchar(max)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(convert(varchar(max),’C’),11000)
    SELECT LEN(@ThirdString) LenThirdString,@ThirdString;

  60. See MSDN:
    “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.”

  61. DECLARE @ThirdString VARCHAR(max)
    SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

    –It will assume the type as varchar default so we have to define the type.

  62. Replicate function returns type is varchar(8000) , so that above command returns 8000 when length exceed,
    We can achieve desired output by following
    set @ThirdString=Convert(varchar(max),REPLICATE(‘C’,6000))+REPLICATE(‘C’,5000)
    it will give output as 11000

    or we can write a user defined replicate function with return type varchar(max)

  63. I think it is limit one time setting value to local variable,no problem of REPLICATE function,see following code

    DECLARE @ThirdString varchar(max)
    SET @ThirdString = REPLICATE(‘a’,8000)+’NewString’
    SELECT LEN(@ThirdString) ;–return 8000
    SET @ThirdString =@ThirdString+’NewString’
    SELECT LEN(@ThirdString) ;–return 8009
    SET @ThirdString= @ThirdString+REPLICATE(‘a’,11000)
    SELECT LEN(@ThirdString) ;–return 16009

    • another reason can be
      varchar(max) have max memory allocation size=8000

      but when you add any new thing to varchar(max) variable, max memory allocation increase by 8000

  64. Because in Varchar(max), allowed max size in 8000 that’s why length of the @ThirdString is only 8000 .

    In my knowledge we can change the @ThirdString query as
    DECLARE @ThirdString varchar(max)
    SET @ThirdString = REPLICATE(‘C’,11000)
    SET @ThirdString = CAST(@ThirdString AS VARCHAR(max)) + CAST(@ThirdString AS VARCHAR(3000))
    SELECT Len(@ThirdString) LenThirdString;
    this will give us 11000 output

  65. From Books Online: “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.”. Our string_expression is not of type varchar(max) or nvarchar(max) so the value gets truncated (it’s char so we get 8000, if it were nchar, we’d get 4000). In order to display 11000 characters, we need to cast the value:
    SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)

  66. The reason is the constant ‘C’ is not varchar(max) but rather varchar therefore, it will go only to the max size for a varchar datatype (8000).
    If you’d made it an nvarchar, you’d max out at Len= 4000 (i.e. replicate(N’c’, 11000))

    Thus, if you cast it to varchar(max) you’ll get the expected result. so select @thirdstring=replicate(cast(‘c’ as varchar(max)), 11000).

  67. it’s Very simple In One of the my Project i face same
    and solution as follows
    Just use cast funcation as show in example and insert string more than 8000

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  68. REPLICATE had a length limit of 2GB of large type in SQL 2005 but as far as I know there is no upper limit in 2008 R2. The correct answer is below:

    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)), 11000)

    Another way to solve this is to define another variable as varchar(max) and then contact two strings before calculating the length. Like below:

    DECLARE @ThirdString1 VARCHAR(MAX)
    DECLARE @ThirdString2 VARCHAR(MAX)
    SET @ThirdString1 = REPLICATE(‘C’, 7000)
    SET @ThirdString2 = REPLICATE(‘C’, 4000)
    SELECT Len(@ThirdString1 + @ThirdString2) LenThirdString;

  69. Puzzle1:
    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.

    Puzzle2:
    We have to cast first parameter of REPLICATE() function to VARCHAR(MAX) in the case of greater than 8000 characters
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

    — see below script
    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  70. DECLARE @ThirdString VARCHAR(MAX)
    Declare @Newstring varchar(max)
    Set @Newstring = ‘C’
    SET @ThirdString = REPLICATE(@Newstring,11000)
    SELECT LEN(@ThirdString) LenThirdString;

  71. And this way too
    DECLARE @ThirdString VARCHAR(MAX)
    SET @ThirdString = REPLICATE(convert(varchar(max),’C’),11000)
    SELECT LEN(@ThirdString) LenThirdString;

  72. while @Thirdstring is Varchar(max), the replicate function result is getting truncated to 8000 characters, so you have to cast the ‘C’ character like this:

    DECLARE @ThirdString as varchar(max)
    SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)
    SELECT LEN(@ThirdString) LenThirdString;

    btw.. I’ve learnt a lot from your blog the past 4 years and would like to thank you for spreading the knowledge.

  73. I guess ‘C’ assigned to default data type char/varchar and its maximum size is 8000.. so even when you replicate 11,000 character it will take only max 8000 character and assigned result to @ThirdString even @ThirdString is varhcar(max). Correct me if I am wrong. Thanks

  74. replicate returns 8,000 bytes.

    DECLARE @a varchar(max)

    SET @a = REPLICATE(CAST(‘x’ AS varchar(max)), 11000)

    SELECT LEN(@a)

  75. 1) I guess REPLICATE function, by design, can return only upto 8000 chars.

    2) To fix this, replace the 2nd last line as below:

    SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),’C’),11000)

  76. You need to use Cast inside replicate to convert it to varchar(max). Like this:

    Set @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
    Select LEN(@ThirdString) LENThirdString

    This should return LEN as 11000

  77. Quiz 1: The reason is that ‘C’ is not VARCHAR(MAX), so result of REPLICATE is not varchar(max) either.

    Quiz 2: SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX), ‘C’),11000)

  78. Hi Pinal,

    its working for me, please find my below response :

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    DECLARE @c VARCHAR(MAX)

    SET @c=’C’

    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;

    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;

    SET @ThirdString = REPLICATE(@c,11000)
    SELECT LEN(@ThirdString) LenThirdString;

  79. DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    DECLARE @c VARCHAR(MAX)

    SET @c=’C’

    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;

    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;

    SET @ThirdString = REPLICATE(@c,11000)
    SELECT LEN(@ThirdString) LenThirdString;

    Quiz 1:
    Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

    Varchar holds only 8000 characters.
    Here is the second part of the Quiz.

    Quiz 2:

    What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

    Assign the sting (C)in to new declared variable for third one

  80. DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    DECLARE @sql nvarchar(max)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = N’select’+ CONVERT(NVARCHAR(MAX),REPLICATE(‘C’,6000))
    SET @sql =@ThirdString + CONVERT(NVARCHAR(MAX),REPLICATE(‘C’,4994))
    SELECT LEN(@sql) LenThirdString;

  81. Because ‘C’ gets replicated 11000 times and can’t be stored in the varchar @ThirdString that can only hold a maximum of 8000! Remaining 3000 is left!
    So we can use two strings and sum them up and finally calculate the answer!

  82. DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)

    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(‘C’,11000)

    SET @ThirdString = @ThirdString + REPLICATE(‘C’, 3000)
    SELECT LEN(@ThirdString)

  83. DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)

    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(‘C’,11000)

    SET @ThirdString = @ThirdString + REPLICATE(‘C’, 3000)
    SELECT LEN(@ThirdString

  84. the return type of the replicate function is based on the input first value , which is here is ‘C’ and is varchar(1) , so the return value is varchar(n) which could not be more than 8000.

    the solution is :

    DECLARE @ThirdString VARCHAR(MAX)
    DECLARE @char VARCHAR(MAX)
    set @char = ‘C’

    SET @ThirdString = REPLICATE(@char,11000)
    SELECT LEN(@ThirdString) LenThirdString;

  85. Answer to Quiz 1 – The REPLICATE function truncates the return value at 8000 bytes if the first parameter is not of VARCHAR(MAX) or NVARCHAR(MAX) types.

    Answer to Quiz 2 – The following changes needs to be done in query to get the return value of 11000
    SET @ThirdString = ‘C';
    SELECT LEN(REPLICATE(@ThirdString, 11000)) AS LenThirdString

  86. –There’s gotta be a simpler solution than this!!!!!!!!!!!
    –The question aught to be how to replicate more than 8000 bytes. Only in varchar is that > 8000 charaters
    use tempdb
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    if (OBJECT_ID(‘dbo.MyReplicate’) is null)
    exec (‘CREATE FUNCTION dbo.MyReplicate() returns sysname AS BEGIN return N”t”; END’)
    GO
    — =============================================
    — Author: Ken
    — Create date: 5/14/2013
    — Description: Replicates over 8000 bytes
    — =============================================
    ALTER FUNCTION dbo.MyReplicate
    (@target nvarchar(max) , @num int)
    RETURNS nvarchar(max)
    AS
    BEGIN
    IF (@target IS NULL OR @num < 1) RETURN NULL
    –LEN won't count trailing blanks add a non-blank and subtract it from computed LEN
    DECLARE @Result nvarchar(max) = '', @len int = LEN(@target + N'a') – 1, @numsaved int = 0
    DECLARE @loopnum int = 4000/@len

    IF (@num = 1) RETURN @target
    WHILE @numsaved @num
    SET @loopnum = @num – @numsaved
    SELECT @Result += REPLICATE( @target, @loopnum), @numsaved += @loopnum
    END
    END
    — Return the result of the function
    RETURN @Result
    END
    GO
    declare @val nvarchar(max) =dbo.MyReplicate(‘T ‘,11000)
    SELECT LEN(@val)LengthVal, @val value
    SET @val = dbo.MyReplicate(‘Tas’,11000)
    SELECT LEN(@val)LengthVal, @val value

  87. Ans 1 — Varchar (Max) will read till 8000 characters.

    ANS 2 — Write this way
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(‘C’,5500)

    –SELECT @ThirdString LenThirdString;

    SELECT LEN(@ThirdString)+LEN(@ThirdString) LenThirdString;

  88. Answer to Q1:
    Replicate function basically does the addition of first parameter to itself (provided its not an integer/decimal)
    n number of times (where n is second parameter), and returns the result in respective data type as that of first parameter. Thus, when we pass a char value as 1st parameter it returns nvarchar result, which is bounded by 8000 character limit. Hence, @ThirdString gets trimmed result of length 8000.

    Answer to Q2:
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
    above query will give output as 11000.
    Reason: casting char ‘C’ as varchar(MAX) will force the output of Replicate to be VARCHAR(MAX) which has maximum size of 2^31-1 characters. This will be able to store our 11000 characters and will give proper result.

  89. Declare @txt varchar(MAX)
    Set @txt = replicate(cast(‘A’ as varchar(max)), 11000)
    Select Len(@txt)

  90. You can use a convert statement around the ‘C’ string:

    SET @ThirdString = REPLICATE(CONVERT(varchar(max),’C’),11000)

    Then the LEN (or DATALENGTH) function works properly

  91. the string expression has to be explicitly cast as a large datatype?

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(max)
    DECLARE @myChar VARCHAR(MAX)
    SET @myChar = ‘C’
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(@myChar,11000)
    SELECT LEN(@ThirdString) LenThirdString;

    returns 11000

  92. Ans for Question 1 : VARCHAR(MAX) could store up to 8000 charecters.
    Ans for Question 2 : We can use VARCHAR(1100) instead of VARCHAR(MAX)

  93. since the input and output are of the same type, cast ‘C’ to varchar(max) before passing it into REPLICATE so the output will also be varchar(max) instead of varchar, which is limited to 8000

  94. Pingback: SQL SERVER – Solution to Puzzle – REPLICATE over 8000 Characters | SQL Server Journey with SQL Authority

  95. The solution is Casting to the type inside de Replicate Function:
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

  96. MSDN: REPLICATE ( string_expression ,integer_expression )
    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.

    So to get more than 11000 char, third expression should be like-
    SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)

  97. SIr if i just change the query like this….

    DECLARE @FirstString VARCHAR(MAX)
    DECLARE @SecondString VARCHAR(MAX)
    DECLARE @ThirdString VARCHAR(MAX)
    SET @FirstString = REPLICATE(‘A’,4000)
    SELECT LEN(@FirstString) LenFirstString;
    SET @SecondString = REPLICATE(‘B’,8000)
    SELECT LEN(@SecondString) LenSecondString;
    SET @ThirdString = REPLICATE(N’C’,11000)
    SELECT LEN(@ThirdString) LenThirdString;

    just using N while setting Third string
    the result is showing 4000 for Third string..
    Eagerly waiting for the answer ….

  98. if i put value anything below 8000 in place of 11000 it gives expected result…but any thing above 8000 gives 4000 as result

  99. REPLICATE ( string_expression ,integer_expression )
    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.

  100. I put the SET into two parts

    SET @ThirdString = REPLICATE(‘C’ ,10000)
    SET @ThirdString=@ThirdString+REPLICATE(‘C’,3000)
    SELECT LEN(@ThirdString) LenThirdString;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s