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 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;
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
–ANITA KAMAT
SET @ThirdString = @ThirdString + REPLICATE(‘C’, 11000)
SELECT LEN(@ThirdString)
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;
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!
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)
Varchar(Max) has limitation of 8000 Characters , we can use Text in place of 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(‘C’,11000)
SET @ThirdString = @ThirdString + REPLICATE(‘C’, 3000)
SELECT LEN(@ThirdString
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;
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
–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
By the way, what is posted is NOT what I wrote.
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;
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.
Hi Pinal.I think Replicate will work perfectly,But len function returntype(size) is the problem.
Declare @txt varchar(MAX)
Set @txt = replicate(cast(‘A’ as varchar(max)), 11000)
Select Len(@txt)
You can use a convert statement around the ‘C’ string:
SET @ThirdString = REPLICATE(CONVERT(varchar(max),’C’),11000)
Then the LEN (or DATALENGTH) function works properly
the string expression has to be explicitly cast as a large datatype?
DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(max)
DECLARE @myChar VARCHAR(MAX)
SET @myChar = ‘C’
SET @FirstString = REPLICATE(‘A’,4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE(‘B’,8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE(@myChar,11000)
SELECT LEN(@ThirdString) LenThirdString;
returns 11000
What is the Correct answer plz
Ans for Question 1 : VARCHAR(MAX) could store up to 8000 charecters.
Ans for Question 2 : We can use VARCHAR(1100) instead of VARCHAR(MAX)