number of character more than 8000!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

number of character more than 8000!

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2011-07-08 : 06:11:04

Hello!

I have a very useful sql expression that looks like

declare @str varchar(max);

select @retstr = COALESCE(@retstr + ',','') + T.[FieldName]
from
(
SELECT [TABLE_NAME],[ORDINAL_POSITION],[COLUMN_NAME] AS [FieldName]
FROM DB.INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] like 'Table01'
) T

print @retstr

Unfortunate i discovery that the number of the tables fieldnames character succeed more than 8000!

Now how could this be solved ?

I need a delimiter-field-string like this one,
but that i don't have to worry about the number of character it is.

Note: can varbinary be used somehow, the question is than how ?

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 06:19:09

You should be able to do it with varchar(max). I assume @str vs @retstr is a typo and that both those refer to the same variable.

When you print, you may not see all the 8000 characters - but that is probably an artifact of SQL server management studio. It may not print out more than 8000 characters. If you do select len(@str), you can verify if it indeed is more than 8000 characters.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2011-07-08 : 06:28:03

quote:
Originally posted by sunitabeck

You should be able to do it with varchar(max). I assume @str vs @retstr is a typo and that both those refer to the same variable.

When you print, you may not see all the 8000 characters - but that is probably an artifact of SQL server management studio. It may not print out more than 8000 characters. If you do select len(@str), you can verify if it indeed is more than 8000 characters.

thanks for answering. str and retstr are the same.

You are right it holds more than 8000 character!! how odd isn't that?
i did tried with len and i came up to 10231 character

Now it looks like i can use substring to pick part of it.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources