Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display More Than 8000 Characters (SQL Spackle)


Display More Than 8000 Characters (SQL Spackle)

Author
Message
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
Jeff thanks again, going to be adding it to my tool box. Now wondering if that tool box should be renamed as "ToolboxbyJeffModen"

Yes I know the articles are labeled as "Spackle", but heck with your articles I not only can fill in the cracks, but build an entire wall.

Again thanks.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
nadabadan
nadabadan
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 1018
"t.N BETWEEN 1 AND LEN(@LongString)/@length+1" would list an extra blank row where LEN(@LongString) is a multiple of @length.




DECLARE @longString varchar(max)
DECLARE @val varchar(max)
SET @val = 'abcdefgh'
SELECT @longString = REPLICATE( @val, 10000 ) -- 80000 char length string



If you use the string above to split the string into 8000 char strings, you will get 11 rows instead of the expect 10.

Perhaps

WHERE N <= CEILING( LEN( @longString ) / CONVERT( numeric(10,1 ), @length ) )

OR

WHERE t.N BETWEEN 1 AND CEILING( LEN( @longString ) / CONVERT( numeric(10,1 ), @length ) )

would be better.
thisisfutile
thisisfutile
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 973
nadabadan (1/27/2011)
...you will get 11 rows instead of the expect 10.


In the article, Jeff stated "I don't really care where the line "wraps", nor do I care about trailing spaces". I'm not trying to speak for Jeff (or am I?), but to me an extra row sort of fits into this area that has been purposely ignored. Cosmetics aren't necessarily important for this spackling.

IMHO

A good solution, nonetheless.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45000 Visits: 39880
ColdCoffee (1/26/2011)
Thanks for this cute Spackle,Jeff :-)


You bet, CC. Thanks for stopping by.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45000 Visits: 39880
samir-424465 (1/26/2011)
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.


Hi Samir,

Thanks for stopping by with the tip. I absolutely agree with the above and there are many other methods you can use, as well. That also takes a bit more time than what I need to take when I'm just trying to do a simple check while writing code in SSMS. It can also require elevated privs and not everyone has the privs to run BCP nor write to disk from an SQL Server.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45000 Visits: 39880
chuckh 3191 (1/27/2011)
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
;


Absolutely correct. You can definitely build a quick Tally CTE on the fly if you don't have one. In a "pinch", you could also use the 2,048 numbers (2k5 and up) that are available in the spt_values table located in the master database.

Thanks for the feedback, Chuck.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45000 Visits: 39880
Koen (da-zero) (1/27/2011)
Thanks, great article Jeff.
You find uses for the Tally table everywhere, don't you :-D


Heh... you bet and thanks for the compliment, Koen.

Almost as frequently, I also find uses for "pseudo-cursors" (the loops that occur behind the scenes at the "C" level.).

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45000 Visits: 39880
nigel. (1/27/2011)
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 .:-)


That's one of my most consistent typeo's... date and data. I post an update to the article that Steve can do a replacement with. Thanks for the catch and the compliment, Nigel.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45000 Visits: 39880
Matt Whitfield (1/27/2011)
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... :-)


Hi Matt,

Long time no "see". Thanks for stopping by and thanks for the compliment.

I agree. You'd think there'd be an easier way to do such a thing in SSMS.

If you're not using SSMS, are you using VS? Since I'm such a "data troll", I never get into VS. Assuming you're using VS, how does one see more than 8K characters from a column there?

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45000 Visits: 39880
dave.clark (1/27/2011)
Thanks for the excellent info!
I tackled this issue myself previously, but my solution is not quite as "simple" ;-)

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


Hi Dave,

Thanks for the great feedback.

Before you replace your "lengthy solution", remember that this bit of "SQL Spackle" doesn't care where a "split" occurs and will frequently split the rows right in the middle of a word and may even produce an extra blank row if the number of characters is evenly divisible by 8000. My method is just a "Quick'n'Dirty" way of verifying what's in a column that's too long to see using a simple SELECT.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search