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
The string_expression component of the REPLICATE function needs to be VARCHAR(MAX) or NVARCHAR(MAX) to enable the output string to exceed 8000 characters
So for the last example use:
DECLARE @ThirdString VARCHAR(MAX);
DECLARE @ThirdSource VARCHAR(MAX);
SET @ThirdSource=’C’;
SET @ThirdString = REPLICATE(@ThirdSource,11000);
SELECT LEN(@ThirdString) LenThirdString;
or
DECLARE @ThirdString VARCHAR(MAX);
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000);
SELECT LEN(@ThirdString) LenThirdString;
Answer
Quiz 1 : Last result is 8000, because maximum length of varchar is 8000 (if varchar(max) is not specified). We passed only varchar value to replicate function,so it returns value as varchar(8000)
Quiz 2: To get 11000 result, replace second last line by following line
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
Hi Pinal,
I think there is a typo in Quiz 1, the length of the @ThirdString is 8000 but not 4000. I think it is because the size if varchar(max) is 8000 characters, so when we use it implicitly then it restricted to 8000 characters.
To get the length greater than 8000 characters I used this statement:
SET @ThirdString = REPLICATE(CAST(‘C’ AS varchar(max)),11000);
Best regards,
Arsen
you need to force the data type to varchar(MAX) like so
DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CAST(‘D’AS varchar(MAX)),11000)
SELECT LEN(@ThirdString ) LenThirdString;
Hi Pinal,
Answer1 : — In this case SQL server is displaying varchar maximum length which is 8000.
Answer2 : – To resolve this problem we need to use cast function and statement should be —
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
Hi Pinal,
Answer1 : — In this case SQL server is displaying varchar maximum length which is 8000.
Answer2 : – To resolve this problem we need to use cast function and statement should be —
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
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;
I think 8000 character is the initial max limit for Replicate..to go beyond that we need explicit conversion..I hv just tried it and it worked for me..But please make me clear sir..
Thanx
@TihrdString is return 8000 due to the max value of varchar i.e. 2^31 -1
HI,
Answer 1 :-
The REPLICATE function frequently uses long strings, and therefore is more likely to incur the 4000-character limit on expression length.
Answer 2 :-
REPLICATE function truncate after 8000 character. I came across this problem few months ago, when i was playing with varchar. If we want to show Correct character len, then we have to break it in two parts and explicitly convert it again in Varchar(max). Have a look on below code.
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;
—- INCORRET
SET @ThirdString = REPLICATE(‘C’,11000)
SELECT LEN(@ThirdString) LenThirdString;
—- CORRECT
SET @ThirdString = CAST(REPLICATE(‘C’,5000) as VARCHAR(max))+REPLICATE(‘C’,6000)
SELECT LEN(@ThirdString) LenThirdString;
Thanks
Vinay Kumar
Hi Pinal,
First of all, I am a great fan of your articles and posts… You have really helped me a lot in gaining speed in SQL.
Now the answer:
First the Correct SQL to display as per requirement:
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;
Reason:
Replicate has two parameters: The first one in the character expression and second one is the integer value…
The second one is not of the concern here.
But the Character expression is implicitly convertible to NVARCHAR or NTEXT and by default as we all know it defaults to 8000 characters. So, the output of the Replicate function can never be more than 8000 characters
So in order to get something more than 8000, we will need to CAST the character expression into MAX (VARCHAR/NVARCHAR) and then perform the replicate on the Character. Now since the variable getting assigned to is also specified in MAX, so it will store 11000 characters and LEN will display the correct value.
Quiz 1: Varchar max length is 8000
Quiz 2:
SET @ThirdString = REPLICATE(convert(varchar(MAX), ‘C’), 20000);
SELECT LEN(@ThirdString) LenThirdString;
DECLARE @String1 AS VARCHAR(3000)
DECLARE @String2 AS VARCHAR(6000)
SET @String1=REPLICATE(‘a’,3000)
SET @String2=REPLICATE(‘b’,6000)
SELECT LEN(@String1+@String2) AS Result_String
–RESULT IS 8000
–Now if we change datatype to VARCHAR(MAX)
DECLARE @String1 AS VARCHAR(MAX)
DECLARE @String2 AS VARCHAR(MAX)
SET @String1=REPLICATE(‘a’,3000)
SET @String2=REPLICATE(‘b’,6000)
SELECT LEN(@String1+@String2) AS Result_String
–RESULT IS 9000
–I think whats happening in above query is Result_String is varchar(9000) which exceeds the limit which is VARCHAR(8000)..so to get correct results we need to cast or convert it to varchar(max)
–in second query strings are of VARCHAR(MAX) type so the result will be of varchar(MAX) type and we get the right result..
hello dave
this script will replicate string over 8000 characters
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)
SELECT LEN(@ThirdString) LenThirdString;
Pinal,
My answers are as follows :
Ans 1 :
REPLICATE function will truncate the value at 8000 bytes. We need to explicitly convert to VARCHAR(MAX) for large string (Ref Ans 2).
Ans 2 :
SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),’C’),11000)
SELECT LEN(@ThirdString) LenThirdString;
Note : Please change @ThirdString value 8000 instead of 4000 in Quiz 1.
Is this because Len() can only compute for varchar(8000) and for larger we need to use DATALENGTH?
1) REPLICATE support 8000 at a time
2) 11000 len can be achieve by following way
SET @ThirdString = REPLICATE(‘C’,8000)
SET @ThirdString = @ThirdString + REPLICATE(‘C’,3000)
SELECT LEN(@ThirdString) LenThirdString;
Quiz 1: REPLICATE function truncates the return value at 8,000 bytes.
Quiz 2: This is the full answer.
DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
DECLARE @FourthString VARCHAR(MAX)
DECLARE @FifthString 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)
SELECT LEN(@ThirdString) LenThirdString;
SET @FourthString = REPLICATE(‘C’,3000)
SELECT LEN(@FourthString) LenFourthString;
SET @FifthString = @ThirdString+@FourthString;
SELECT LEN(@FifthString) LenFifthString;
DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000);
SELECT LEN(@ThirdString)
varchar [ ( n | max ) ] Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.
you have to cast replicate character to varchar(max)…
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;