Display More Than 8000 Characters (SQL Spackle)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Display More Than 8000 Characters (SQL...

47 posts, Page 1 of 5

1 2 3 4 5 »»»

Display More Than 8000 Characters (SQL Spackle)

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Wednesday, January 26, 2011 9:26 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

Comments posted to this topic are about the item Display More Than 8000 Characters (SQL Spackle)

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1054372

ColdCoffee

Posted Wednesday, January 26, 2011 11:01 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

Thanks for this cute Spackle,Jeff Smile

Post #1054387

samir-424465

Posted Wednesday, January 26, 2011 11:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 3:13 AM
Points: 98, Visits: 68

We can use BCP utility to get the data of the table (queryout option). The contents of the file will have character field (with varchar(MAX) / nvarchar(MAX)) with all the data in it.

Post #1054389

chuckh 3191

Posted Thursday, January 27, 2011 2:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 08, 2011 2:33 PM
Points: 2, Visits: 9

You don't have to have a Tally table made in your database. Using common table expressions, you can generate them on the fly. Here is such using the example code from the article where we reaplace Tally with cteTally

--===== Declare a couple of long string variables of two different datatypes
DECLARE @LongString VARCHAR(MAX),
@NLongString NVARCHAR(MAX)
;
--===== Fill each string with 10,000 GUIDs followed by a space
-- for a total of 369999 (+1 trailing space) characters.
;WITH
cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP 20000
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT @LongString = (SELECT CAST(NEWID() AS CHAR(36)) + ' '
FROM cteTally t
WHERE t.N BETWEEN 1 AND 10000
FOR XML PATH('')),
@NLongString = @LongString
;
--===== Just confirming the length of the strings here
SELECT LEN(@LongString), LEN(@NLongString)
;
--===== Let's solve the problem with a little control over the width
-- of the returned data. This could easily be converted into
-- an inline Table Valued Function.
DECLARE @Width INT;
SELECT @Width = 8000;

--===== Show that the solution works on VARCHAR(MAX)
;WITH
cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP (LEN(@LongString))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT StartPosition = (t.N-1)*@Width+1,
SliceData = SUBSTRING(@LongString,(t.N-1)*@Width+1,@Width)
FROM cteTally t
WHERE t.N BETWEEN 1 AND LEN(@LongString)/@Width+1
;

Post #1054437

Koen Verbeeck

Posted Thursday, January 27, 2011 3:02 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410, Visits: 6,495

Thanks, great article Jeff.
You find uses for the Tally table everywhere, don't you BigGrin

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1054442

nigel.

Posted Thursday, January 27, 2011 3:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 03, 2013 9:46 AM
Points: 1,164, Visits: 2,603

Thanks Jeff, nice article.

Spotted a minor typo in the first code example, has SliceDate instead of SliceData for the column alias.
I know it has no real effect. But, knowing what a perfectionist you are . Smile

--
Nigel

Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1054457

Matt Whitfield

Posted Thursday, January 27, 2011 4:01 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718

Good article Jeff, nice one.

It does surprise me, though, that it's even necessary. I mean, it's 2011...

I guess that sort of thing is why I very rarely use SSMS... Smile

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone

Post #1054463

dave.clark

Posted Thursday, January 27, 2011 4:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 8:39 AM
Points: 42, Visits: 353

Thanks for the excellent info!
I tackled this issue myself previously, but my solution is not quite as "simple" Wink

I'll have to test this and see if I can replace my lengthy solution.

Post #1054474

thisisfutile

Posted Thursday, January 27, 2011 7:22 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 10:11 AM
Points: 56, Visits: 492

nigel. (1/27/2011)

I noticed that too. Is it wrong for me to find some sort of comfort in knowing that even Jeff Moden makes the date/data typo? Tongue

If I had a dollar for every time I've done that...

Thanks for the article Jeff. I always enjoy seeing how you solve "the loop" issue.

Post #1054566

nigel.

Posted Thursday, January 27, 2011 7:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 03, 2013 9:46 AM
Points: 1,164, Visits: 2,603

thisisfutile (1/27/2011)

nigel. (1/27/2011)

... Is it wrong for me to find some sort of comfort in knowing that even Jeff Moden makes the date/data typo? Tongue

No, definitely not Smile

--
Nigel

Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1054579

« Prev Topic | Next Topic »

47 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions