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 (https://blog.sqlauthority.com)
154 Comments. Leave new
Hi, so, the answer really is not to hard to find, you just need to visit msdn: https://docs.microsoft.com/en-us/sql/t-sql/functions/replicate-transact-sql?view=sql-server-2017 -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 ;)
In replicate if string expression is not of Varchar(Max) type then replicate truncate its length to 8000
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 )
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.
CAST ‘C’ TO VARCHAR(MAX)
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)
SET @ThirdString = REPLICATE(‘C’,8000)
set @ThirdString = @ThirdString + REPLICATE(‘C’,3000)
another solution :
SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)
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;
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?
…
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;
You must replace REPLICATE(‘C’, 11000) with REPLICATE(CAST(‘C’ AS VARCHAR(MAX)), 11000) due to implicit casting.
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!
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)
DECLARE @FirstString VARCHAR(MAX)
SET @FirstString = REPLICATE(Convert(Varchar(max),’A’),11000)
SELECT LEN(@FirstString) LenFirstString;
SELECT does not take more than 8K characters.
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;
It works like this because in Len funtion it won’t cast automatically.
SELECT LEN( CAST(@Thirdstring AS VARCHAR(MAX)))
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;
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.”