August 21, 2008 at 11:04 am
I am having a requiremet where i have to concatenate a long string.
I have tried both
varchar(max)
nvarchar(max)
In case of varchar(max) concatenation stops after 8000 characters
In case of nvarchar(max) concatenation stops after 4000 characters
I have to append string length more than the 8000 characters. Whats the solution for this in sql server 2005.
Regards,
Gaurav
August 21, 2008 at 11:21 am
You have to cast/convert the strings to varchar(max) BEFORE you concatenate them, or it will cut them off.
Something like this:
declare @MainString varchar(max), @Substring1 varchar(8000), @Substring2 varchar(8000)
select @substring1 = replicate('=', 8000), @substring2 = replicate('/', 8000)
select @mainstring = cast(@substring1 as varchar(max)) + cast(@substring2 as varchar(max))
select len(@mainstring)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2008 at 11:22 am
You will need to explicitly cast the variable as (n)varchar(max)
for example
declare @STR varchar(max)
select @STR = replicate('a', 9001)
select len(@str) --returns 8000
select @STR = replicate(cast('a' as varchar(max)), 9001)
select len(@str) --returns 9001
I had recently found this out from an online article http://www.sqlserverandxml.com/2008/01/varcharnvarchar-n-vs-max.html
that all string functions that take varchar data type assume the variable to be 8000 characters unless one of the parameters is varchar(max) or 4000 in case of nvarchar(max).
hope this helps.
August 22, 2008 at 9:22 am
Following is the string generated from the code:
FETCH NEXT FROM CATEGORYIDCURSOR INTO @columnName
WHILE @@FETCH_STATUS=0
BEGIN
SET @CategoryIDString= CONVERT(nvarchar(max),(CONVERT(nvarchar(max),@CategoryIDString)+ convert(nvarchar(max),'SELECT [NAME] FROM category where categoryid=')+convert(nvarchar(max),@columnName) +convert(nvarchar(max),' UNION ALL ')))
PRINT @CategoryIDString
FETCH NEXT FROM CATEGORYIDCURSOR INTO @columnName
END
CLOSE CATEGORYIDCURSOR
DEALLOCATE CATEGORYIDCURSOR
This is the maximum length of string being generated. Beyond this it is not concatenating the string. Any suggestions please:
SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9001 UNION ALL SELECT [NAME] FROM category where categoryid=9001 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9002 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9003 UNION ALL SELECT [NAME] FROM category where categoryid=9004 UNION ALL SELECT [NAME] FROM category where categoryid=9004 UNION ALL SELECT [NAME] FROM category where categoryid=9005 UNION ALL SELECT [NAME] FROM category where categoryid=9005 UNION ALL SELECT [NAME] FROM category where categoryid=9005 UNION ALL SELECT [NAME] FROM category where categoryid=9005 UNION ALL SELECT [NAME] FROM category where categoryid=9005 UNION ALL SELECT [NAME] FROM category where categoryid=9005 UNION ALL SELECT [NAME] FROM category where categoryid=9005 UNION ALL SELECT [NAME] FROM category where categoryid=9005 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9006 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9007 UNION ALL SELECT [NAME] FROM category where categoryid=9014 UNION ALL SELECT [NAME] FROM category where categoryid=9014 UNION ALL SELECT [NAME] FROM category where categoryid=9014 UNION ALL SELECT [NAME] FROM category where categoryid=9014 UNION ALL SELECT [NAME] FROM category where categoryid=9014 UNION ALL SELECT [NAME] FROM category where categoryid=9014 UNION ALL SELECT [NAME] FROM category where categoryid=9015 UNION ALL SELECT [NAME] FROM category where categoryid=9015 UNION ALL SELECT [NAME] FROM category where categoryid=9015 UNION ALL SELECT [NAME] FROM category where categoryid=9015 UNION ALL SELECT [NAME] FROM category where categoryid=9015 UNION ALL SELECT [NAME] FROM category where categoryid=9016 UNION ALL SELECT [NAME] FROM category where categoryid=9016 UNION ALL SELECT [NAME] FROM category where categoryid=9016 UNION ALL SELECT [NAME] FROM category where categoryid=9016 UNION ALL SELECT [NAME] FROM c
August 22, 2008 at 9:27 am
Someone may correct me, but I believe there is an issue with the print statement truncating. Check the length of the string to see if it is what you expect versus the print command.
August 22, 2008 at 10:23 am
yes problem is of print statement. But whats the solution to see the full string generated.
ALso once the string is generated i want to execute it now.
i m using
execute sp_executesql @GeneratedString
But it appears that sp_executesql does not recognize string in nvarchar(max) format.
August 22, 2008 at 10:51 am
Print does cut off after a certain point. You might be able to change it to select (instead of print) and output to a file or to text. There are options that open up how much it will select.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 22, 2008 at 1:56 pm
you can look into using ntext or maybe varbinary(max).
ntext will be removed in future releases.
varbinary(max)
Variable-length binary 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 the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The SQL-2003 synonym for varbinary is binary varying.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply