SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".
--Phil McCracken
Introduction
Every once in a while during troubleshooting or sometimes even during development, I need to examine the content of a VARCHAR(MAX) or NVARCHAR(MAX) datatype. It could be just to visually verify the data or maybe it's to examine a particularly long bit of dynamic SQL.
Whatever the case, the problem that all of us share is that SSMS will only display the first 8000 characters of any variable or column. Here's a really quick hack to fill in that crack so we can see all of the characters...
Preparation
A Tally Table (also known as a "table of numbers) makes life really simple. If you don't know what a Tally Table is or how it can be used to replace certain types of loops and other forms of RBAR (including many forms of recursion), then please see the following article because we're going to use the Tally Table to generate some test data and to solve the problem.
http://www.sqlservercentral.com/articles/T-SQL/62867/
Slicing the Problem Up
We've already said that we can't display more than 8,000 characters at a time. We can, however, take sub-strings from anywhere we want in the larger string. We don't need anything sophisticated that will break in all the right spots. We just want to see all of the data and maybe present it in a bit of a usable manner. This is where some integer math will come into play.
Calculating the Slices
If we have a string stored in one of the MAX datatypes (whether it's a variable or a column) that's, say, 22,000 characters long, we're going to need three 8000 character slices. Like this...
Slice# Start Position End Position ------ -------------- ------------ 1 1 8000 2 8001 16000 3 16001 24000
Obviously, we don't need the full 24,000 bytes because our string only goes out to 22,000 bytes, but we still need that 3rd slice to cover it.
Let's do some calculations we'll need to "cut" the slices using the integer math I promised. Since I want something easy to remember that will work for both VARCHAR(MAX) and NVARCHAR(MAX), I don't really care where the line "wraps", nor do I care about trailing spaces (for this), I just use the LEN function instead of DATALENGTH. LEN returns a BIGINT datatype for the MAX datatypes.
Using the same slice table from above, if we divide the "End Position" minus 1 by 8000, here's what we get (see Quotient or "Q" column)...
Slice# Start Position End Position "Q"uotient Q*8000+1 ------ -------------- ------------ ---------- -------- 1 1 8000 0 1 2 8001 16000 1 8001 3 16001 24000 2 16001
Because it's all Integer math, our result is the number with any remainder simply being dropped. If we multiply that number (we'll call it "Q") times 8000 and add 1 to that, we end up with the correct starting position for each slice. Also notice that if we simply add 1 to the value of "Q", we end up with the Slice # and the last Slice # is the number of slices we need to make.
Sounds VERY complex, doesn't it? And, it sounds like we're going to need a loop...
Translation to Code
Thanks to the Tally Table and the Integer math, the code isn't only simple, it doesn't need a loop or any other form of RBAR to solve this problem. Here's the code:
SELECT StartPosition = (t.N-1)*8000+1,
SliceDate = SUBSTRING(@LongString,(t.N-1)*8000+1,8000)
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND LEN(@LongString)/8000+1
I use a Tally Table that starts at "1" but, because of the BETWEEN I used above, this code will also work for Tally Table's that start with "0".
Note that (t.N-1) is the "Q" value that we spoke of before. The value of t.N will always be from 1 to some maximum number of slices. The maximum number of slices is calculated in a manner very similar to how we calculated "Q" manually. In the code, we just take the total length of the string, divide it by 8000 (the slice size), and add 1 to it.
Again, the code doesn't do any special character recognition for wrapping but it's very simple code. Note that if you run the code in Text mode instead of the Grid mode, things like dynamic SQL which may have carriage returns in them will still cause a carriage return making the output look nearly as good as the original. Just remember that we force a break at multiples of 8000 characters and that can happen in the middle of a word.
Test Code
Heh... I can't walk away from an article without providing a bit of test code. The following code uses a Tally Table (that has a max value of at least 10,000) to first create some wide test data and then again in the solution code just like we did earlier. My favorite number for the value of @Width is 100 and I think you'll see why when you run the code with different values for @Width.
--===== 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.
SELECT @LongString = (SELECT CAST(NEWID() AS CHAR(36)) + ' '
FROM dbo.Tally 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)
SELECT StartPosition = (t.N-1)*@Width+1,
SliceData = SUBSTRING(@LongString,(t.N-1)*@Width+1,@Width)
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND LEN(@LongString)/@Width+1
;
--===== Show that the solution works on NVARCHAR(MAX)
SELECT StartPosition = (t.N-1)*@Width+1,
SliceData = SUBSTRING(@NLongString,(t.N-1)*@Width+1,@Width)
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND LEN(@NLongString)/@Width+1
;
Crack Filled!
Thanks for listening, folks.
--Jeff Moden