January 27, 2011 at 10:20 am
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
Change is inevitable... Change for the better is not.
January 27, 2011 at 10:26 am
Koen (da-zero) (1/27/2011)
Thanks, great article Jeff.You find uses for the Tally table everywhere, don't you 😀
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
Change is inevitable... Change for the better is not.
January 27, 2011 at 10:30 am
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
Change is inevitable... Change for the better is not.
January 27, 2011 at 10:34 am
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
Change is inevitable... Change for the better is not.
January 27, 2011 at 10:37 am
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
Change is inevitable... Change for the better is not.
January 27, 2011 at 10:44 am
thisisfutile (1/27/2011)
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 .:-)
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? 😛
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.
Heh... I agree... As you say, if I has a dollar for every time I've done that, I'd be able to retire on the earnings and maybe help others retire as well. It shows that I really am "human" and even I take some comfort in that. 😛
Shifting gears... the real reason I try to "avoid the loop" isn't always because of performance issues (there are some loops that are better for performance than setbased methods but not many). The real reason is {insert drum roll here}... I'm lazy. 😀 Writing loops in a declarative language takes me too long to think about and they usually involve more typing. :w00t:
Thanks for the read and the compliment.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2011 at 10:54 am
bitbucket-25253 (1/27/2011)
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.
A long, long time ago, I started to put together a book of SQL and "Power Basic" tips for some of the folks I worked for. I couldn't think of a name for it and didn't want to be so pompous as to call it something with the word "bible" in it, so I borrowed from one of "Flip Wilson's" standup comedies and called it "The Jeff Moden Book of Boy-Howdy!". 😛
Thanks for the great feedback and the awesome compliment. I very much look forward to meeting you at SQLSaturday #60. If you send me an email, I'll send you my cell phone number so we don't somehow miss each other.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2011 at 11:11 am
nadabadan (1/27/2011)
"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.
Absolutely correct and thanks for posting your alternate formula.
It's good that you point this out if someone needs a bit more serious code for production. Of course, then they should also account for where individual lines break so they don't break in the middle of a word, etc. My simple Quick'n'Dirty check wasn't meant do any formatting other than making it so a Developer could do a quick verification as to what was in a column wider than 8K character.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2011 at 11:20 am
thisisfutile (1/27/2011)
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.
Exactly. It's just a bit of Quick'n'Dirty code where cosmetics weren't considered at all. Thanks for jumping in.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2011 at 11:30 am
thisisfutile (1/27/2011)
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.
I think it is a great solution as well and am a fan of the tally table.
"to me an extra row sort of fits into this area that has been purposely ignored."
Very well could be. I'd rather correct it since it is such a minor change.
Cosmetics aren't necessarily important for this spackling.
Another valid point. But like I said before, why not correct it and have it done with.
January 27, 2011 at 11:55 am
Jeff Moden (1/27/2011)
bitbucket-25253 (1/27/2011)
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.
A long, long time ago, I started to put together a book of SQL and "Power Basic" tips for some of the folks I worked for. I couldn't think of a name for it and didn't want to be so pompous as to call it something with the word "bible" in it, so I borrowed from one of "Flip Wilson's" standup comedies and called it "The Jeff Moden Book of Boy-Howdy!". 😛
Thanks for the great feedback and the awesome compliment. I very much look forward to meeting you at SQLSaturday #60. If you send me an email, I'll send you my cell phone number so we don't somehow miss each other.
Sent to your work email address at 1:52 PM
January 27, 2011 at 1:12 pm
All valid techniques, but if you just want to see the content of a long field then the easiest way is to copy the value (ctrl-c) from the cell and paste it (ctrl-v) into the query window.
Matt.
January 27, 2011 at 2:09 pm
matt stockham (1/27/2011)
All valid techniques, but if you just want to see the content of a long field then the easiest way is to copy the value (ctrl-c) from the cell and paste it (ctrl-v) into the query window.Matt.
Try it and tell us how it went.
January 27, 2011 at 2:19 pm
nadabadan (1/27/2011)
matt stockham (1/27/2011)
All valid techniques, but if you just want to see the content of a long field then the easiest way is to copy the value (ctrl-c) from the cell and paste it (ctrl-v) into the query window.Matt.
Try it and tell us how it went.
Went fine, does it not work for you?
January 27, 2011 at 2:30 pm
matt stockham (1/27/2011)
nadabadan (1/27/2011)
matt stockham (1/27/2011)
All valid techniques, but if you just want to see the content of a long field then the easiest way is to copy the value (ctrl-c) from the cell and paste it (ctrl-v) into the query window.Matt.
Try it and tell us how it went.
Went fine, does it not work for you?
DECLARE @v-2 varchar(max)
DECLARE @t varchar(max)
SET @t = 'abcdefghij'
SELECT @v-2 = REPLICATE( @t, 10000 )
SELECT @v-2
If you copy and paste the value in the cell, what is the length of the copied string? 100000 or 65535?
Viewing 15 posts - 16 through 30 (of 67 total)
You must be logged in to reply to this topic. Login to reply