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

  • the max .value of varchar is 8000 so that’s why it is not give the result more than 8000

    Reply
  • We already have 99 valid answers – looking forward to 100th correct answer!

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

    Reply
  • Olga Medvedeva
    May 14, 2013 10:03 pm

    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)

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

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

    Reply
  • –ANITA KAMAT

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

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

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

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

    Reply
  • Varchar(Max) has limitation of 8000 Characters , we can use Text in place of Varchar(Max)

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

    Reply
  • While the results of this question will be announced on Friday – do not forget to participate in another contest! You can win a really cool gadget. 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
  • 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;

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

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

    Reply
  • By the way, what is posted is NOT what I wrote.

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

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

    Reply
  • Hi Pinal.I think Replicate will work perfectly,But len function returntype(size) is the problem.

    Reply

Leave a Reply