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
we can use userdefine datatype instead of varchar(max)…
SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),’C’),11000)
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)
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
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;
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.
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;
SET @ThirdString = REPLICATE(CAST(‘C’ as VARCHAR(MAX)),11000)
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;
Varchar’s maximum string length can be 8000. User define data type can be used
Answer for Quiz 1 : The length of the @ThirdString is shown 8000 because varchar(max) can take maximum 8000 characters.
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000 )
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
SELECT LEN(@thirdString) LenThirdString
This should do the trick.
Regards,
Sreekanth
REPLICATE truncates the return value at 8,000 bytes therefore max values as length shown in result here is 8000.
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;
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.
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;
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
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;
set @firststring = replicate(cast(‘A’ as varchar(max)),11000)