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:

SQL SERVER - Puzzle and Answer - REPLICATE over 8000 Characters replicatepuz1

`

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:

SQL SERVER - Puzzle and Answer - REPLICATE over 8000 Characters replicatepuz2

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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT
Next Post
SQL SERVER – How to use xp_sscanf in Real World Scenario?

Related Posts

154 Comments. Leave new

  • Ahmet Firat Tasatmanlar
    May 15, 2013 9:27 pm

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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Eng.Motafa Elmasry
    May 16, 2013 3:54 am

    What is the Correct answer plz

    Reply
  • 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)

    Reply
  • exec sp_configure ‘max text repl size (B)’,2147483647;
    reconfigure

    Reply
  • SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX), ‘C’), 110000);
    SELECT LEN(@ThirdString) LenThirdString;

    Reply
  • 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

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

    Reply
  • 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)

    Reply
  • Thank you all for participating in the contest. Here is another contest where you can Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C. Do not forget to participate in the contest – https://blog.sqlauthority.com/2013/05/15/sql-server-sql-puzzle-of-set-ansi_null-win-usd-250-worth-amazon-gift-cards-and-bubble-copter-rc/

    Reply
  • 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 ….

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

    Reply
  • I got the answer ….
    Thanx :-)

    Reply
  • Ganesh Vasanth
    August 26, 2013 12:19 pm

    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.

    Reply
  • I put the SET into two parts

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

    Reply

Leave a Reply

Menu