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
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.
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)
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
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
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)
Sorry, small change
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
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).
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;
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;
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;
DECLARE @ThirdString VARCHAR(MAX)
Declare @Newstring varchar(max)
Set @Newstring = ‘C’
SET @ThirdString = REPLICATE(@Newstring,11000)
SELECT LEN(@ThirdString) LenThirdString;
And this way too
DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(convert(varchar(max),’C’),11000)
SELECT LEN(@ThirdString) LenThirdString;
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.
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
replicate returns 8,000 bytes.
DECLARE @a varchar(max)
SET @a = REPLICATE(CAST(‘x’ AS varchar(max)), 11000)
SELECT LEN(@a)
–Write it as
SET @ThirdString = REPLICATE(CAST(‘C’ AS nvarchar(max)), 11000)
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)
the max .value of varchar is 8000 so that’s why it is not give the result more than 8000
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
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)